DateTime toTime()'s epoch starts at 1970-01-*02*

[soulne4ny]

It looks like this toTime() leads to wrong result

SELECT toDateTime('2018-01-01 00:00:00') + toRelativeSecondNum(toTime(toDateTime('2018-01-01 23:00:00'))) AS t
FORMAT Vertical

Row 1:
──────
t: 2018-01-02 23:00:00

Clues:

1. Addition

SELECT toDateTime('2018-01-01 00:00:00') + 1 AS t
FORMAT Vertical

Row 1:
──────
t: 2018-01-01 00:00:01

2. toTime()

SELECT toTime(toDateTime('2018-01-01 00:00:00')) AS t
FORMAT Vertical

Row 1:
──────
t: 1970-01-02 00:00:00

SELECT toRelativeSecondNum(toTime(toDateTime('2018-01-02 00:00:00'))) AS t
FORMAT Vertical

Row 1:
──────
t: 86400

Version info

• clickhouse-server 1.1.54388 (docker image yandex/clickhouse-server af405db7538d)
• ubuntu 18.04 LTS
• docker 18.03.1-ce

[soulne4ny]

It is implementation, however quite unexpected.
https://github.com/yandex/ClickHouse/blob/13762471da910d5b7ab37c1510616cac24e34ab2/dbms/src/Functions/FunctionsDateTime.h#L191

[filimonov]

Why do you expect it should use another day?

From documentation:

toDate()
Converts a date with time to a certain fixed date, while preserving the time."

1970-01-02 is definitely 'certain fixed date', isn't it? :)

1970-01-01 has a special meaning in clickhouse. It's 'out of the range' date, printed as '0000-00-00'.

toTime is supposed to be able to compare / group times in >=2 different days. Why are you interested in absolute values of that?

[alexey-milovidov]

We use 1970-01-02 because 1970-01-01 00:00:00 MSK has unix timestamp of -10800 that is before unix epoch.

[soulne4ny]

Why are you interested in absolute values of that?
The goal was to combine time part of one DateTime value with date part of another. It was while trying different ways to generate time values in between two moments in time. Final solution contained dates without time. Dates are capable of ±1 and it is enouth.

However, the function name toTime() is misleading despite clear statement about certain fixed date.