INSERT Date from Unix Timestamp

[Alexandr Curtov]

"As an exception, if converting from UInt32, Int32, UInt64, or Int64 type numbers to Date, and if the number is greater than or equal to 65536, the number is interpreted as a Unix timestamp (and not as the number of days) and is rounded to the date." 

But this not working while inserting.

CREATE TABLE test (date Date, unix_ts DateTime) ENGINE = MergeTree(date, (unix_ts), 8192);
INSERT INTO test VALUES (1464364942,1464364942);

And I got:

SELECT *
FROM test

┌───────date─┬─────────────unix_ts─┐

│ 0000-00-00 │ 2016-05-27 09:02:22 │

└────────────┴─────────────────────┘

Of course if I inserting unix days - it works. 

[man...@gmail.com]

As I see, it is implemented for conversion functions (toDate), but not for parsing values directly.
Also, when Date is not in form 'YYYY-MM-DD' during insertion with VALUES format, then slow path for interpreting data is activated.

There is room for improvement.

[Jose Vasconcellos]

I found it easier to define the date field with a default value. In your example, date Date DEFAULT toDate(Unix_ts)

This does the conversion and it's one less field to send.

[Alexandr Curtov]

Thanks! After reading a little I choose to use MATERIALIZED as default.

date Date MATERIALIZED toDate(Unix_ts)

суббота, 5 ноября 2016 г., 0:59:16 UTC+7 пользователь Jose Vasconcellos написал: