I have only 1970-01-1.. when I use ifNull

Question

In ClickHouse, I want to write fix 'dateTime' where column (type: DateTime64(9)) is null but I have only 1970-01-01...

select Id, ifnull(event_datetime, toDateTime64('2023-06-20',9,'Etc/UTC')) as event_datetime from ...

I wrote the correct timeZone like I have in the table, wrote the correct number of 0 after the dot, and checked that I haven't put limits for rewrite values in the table.

Answer 1

It is important to understand that in ClickHouse, "NULL" is only used with the Nullable data type: https://clickhouse.com/docs/en/sql-reference/data-types/nullable - this is a special data type if you want to essentially store a string value of "NULL" rather than an empty field.

In general, I suspect what you trying to do is check for an empty field, or in the case of DateTime, check for '1970-01-01 00:00:00'. Maybe something like this?

SELECT Id, toDateTime64('2023-06-20',9)
FROM dateTest
WHERE event_datetime = '1970-01-01 00:00:00';

Answer 2

Try something like this:

SELECT
    Id, if(event_datetime == toDateTime64('1970-01-01 00:00:00.000', 9), toDateTime64('2023-06-20', 9), event_datetime) AS event_datetime
FROM
    table;