1

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

enter image description here

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.

joeljpa
  • 317
  • 2
  • 13
Khavrenkov Valeriy
  • 11
  • 2

2 Answers2

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';
Thom O'Connor
  • 131
  • 4
  • Thank you for anwer! Unfortunately, no. I want to change all NULL values in Nullable DateTime64(9) column to '2023-06-20 00:00:00' or now() , it's doesn't matter. I use ifnull(event_datetime, toDateTime64('2023-06-20',9,'Etc/UTC') for it but I have only 1970-01-01 instead of '2023-06-20 00:00:00'. – Khavrenkov Valeriy Jun 28 '23 at 19:38
  • please show an example of what is currently in that column, such as just a set of those rows where at least one has no value/NULL - example: SELECT Id, event_datetime FROM dateTest LIMIT 20; – Thom O'Connor Jun 28 '23 at 22:55
1

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;
Gumada Yaroslav
  • 115
  • 1
  • 10