Clickhouse: Datetime condition returns inconsistent result

[doom369]

select * from reporting_events where device_id = 2 and ts >= '1970-01-01 00:00:00';

SELECT *
FROM reporting_events 
WHERE (device_id = 2) AND (ts >= '1970-01-01 00:00:00')

Ok.

0 rows in set. Elapsed: 0.004 sec.

Now, adding 1 day to the string 1970-01-01 00:00:00 and make it 1970-01-02 00:00:00

select * from reporting_events where device_id = 2 and ts >= '1970-01-02 00:00:00';

SELECT *
FROM reporting_events 
WHERE (device_id = 2) AND (ts >= '1970-01-02 00:00:00')

┌─id─┬─device_id─┬──────────────────ts─┬─event_hashcode─┬─type─┬─description─┬─is_resolved─┬─resolved_by─┬─────────resolved_at─┬─resolved_comment─┐
│ 40 │         2 │ 2019-01-28 18:17:08 │      613812780 │    4 │             │           0 │             │ 0000-00-00 00:00:00 │                  │
└────┴───────────┴─────────────────────┴────────────────┴──────┴─────────────┴─────────────┴─────────────┴─────────────────────┴──────────────────┘
┌─id─┬─device_id─┬──────────────────ts─┬─event_hashcode─┬─type─┬─description─┬─is_resolved─┬─resolved_by─┬─────────resolved_at─┬─resolved_comment─┐
│ 39 │         2 │ 2019-01-28 18:17:08 │    -1958892973 │    0 │             │           0 │             │ 0000-00-00 00:00:00 │                  │
└────┴───────────┴─────────────────────┴────────────────┴──────┴─────────────┴─────────────┴─────────────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.005 sec. 

CREATE TABLE reporting_events (
  id UInt64,
  device_id UInt32,
  ts DateTime DEFAULT now(),
  event_hashcode Int32,
  type UInt8,
  description String,
  is_resolved UInt8,
  resolved_by String,
  resolved_at DateTime,
  resolved_comment String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, ts, event_hashcode, type);

I found a similar issue - https://github.com/yandex/ClickHouse/issues/2596. However, comparison result seems wrong to me. How it should be handled if this is expected?

[alexey-milovidov]

1970-01-01 00:00:00 is before unix epoch in timezones that have positive UTC offset at that point of time. The reason of this behaviour is that it overflows to 2106.

This behaviour has to be fixed.