Wrong date format

[sudmed]

Hello experts, I run the last Lightweight version (pg to ch) and on my dataset a date is in wrong format. In postgres table the date format is timestamp with time zone and it looks like 2023-04-21 21:43:11.965283+03. In clickhouse table the date format is DateTime64(6) and it looks like 2023-04-21 18:43:11.965283000. As you can see UTC+3 became UTC.

Is there a way to make the date in CH be the same as PG?

[aadant]

Actually you can change the default timezone for ClickHouse server and it should solve the issue. Reopen if it is not the case.

[sudmed]

Hello, @aadant It is not the case, changing the default timezone for ClickHouse server didn't solve the issue. It seems that Lightweight Replicator doesn't understand timestamp with time zone. If I put timestamp without time zone in postgres, the Lightweight Replicator correctly streams it to CH. You can reproduce the problem:

Create PG table

CREATE TABLE test
(
  id bigserial NOT NULL,
  consultation_id int8 NOT NULL,
  recomendation text NULL,
  date1 timestamp with time zone NOT NULL DEFAULT now(),
  date2 timestamp(0) with time zone NOT NULL DEFAULT now(),
  date3 timestamp with time zone NOT NULL DEFAULT '2023-04-21 21:43:11.965283+03',
  date4 timestamp(0) with time zone NOT NULL DEFAULT '2023-04-21 21:43:32+03',
  date5 timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT protocol_pkey PRIMARY KEY (id)
);

Create CH table

CREATE TABLE test
(
    `id` Int64,
    `consultation_id` Int64,
    `recomendation` Nullable(String),
    `date1` DateTime64(9),
    `date2` DateTime,
    `date3` DateTime64(6),
    `date4` DateTime,
    `date5` DateTime64(6),
    `_sign` Int8,
    `_version` UInt64
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY id;

INSERT in PG

INSERT INTO protocol_test VALUES ('156', '56', 'ттест');

result of Lightweight Replicator

select * from test;
┌──id─┬─consultation_id─┬─recomendation─┬─────────────────────────date1─┬───────────────date2─┬──────────────────────date3─┬───────────────date4─┬───────────────────date5─┬─_sign─┬──────_version─┐
│ 156 │              56 │ ттест         │ 2023-04-22 09:55:18.824971000 │ 2023-04-22 09:55:19 │ 2023-04-21 18:43:11.965283 │ 2023-04-21 18:43:32 │ 2023-04-22 12:55:18.824 │     1 │ 1682157318825 │
└─────┴─────────────────┴───────────────┴───────────────────────────────┴─────────────────────┴────────────────────────────┴─────────────────────┴─────────────────────────┴───────┴───────────────┘

[aadant]

ok @subkanthi let us fix this by supporting the timezone in the DateTime64

[aadant]

@sudmed I took another look, you can make it work if you add the timezone info on the CH table DateTime64. This is not done automatically but this is a workaround until a fix is pushed. You are mixing different TZ in the same table

[aadant]

@subkanthi the issue exists in MySQL too. you need to add the TZ in all DateTime64.

This is something we should fix. Related to other recent issues