英文:
I have only 1970-01-1.. when I use ifNull
问题
在ClickHouse中，我想要修复'dateTime'列（类型为：DateTime64(9)）为空的情况，但我只有1970-01-01...
select Id, ifnull(event_datetime, toDateTime64('2023-06-20',9,'Etc/UTC')) as event_datetime from ...
我已经按照表中的正确时区进行了设置，并在小数点后写入了正确数量的0，并检查了表中是否设置了重新写入值的限制。
英文:
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.
答案1
得分: 1
在ClickHouse中，重要的是要理解，"NULL" 只与 Nullable 数据类型一起使用：https://clickhouse.com/docs/en/sql-reference/data-types/nullable - 这是一种特殊的数据类型，如果你想要存储一个字符串值 "NULL" 而不是一个空字段。
一般来说，我怀疑你想要做的是检查一个空字段，或者在 DateTime 的情况下，检查是否为 '1970-01-01 00:00:00'。也许像这样？
SELECT Id, toDateTime64('2023-06-20', 9)
FROM dateTest
WHERE event_datetime = '1970-01-01 00:00:00';
英文:
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';
答案2
得分: 1
尝试类似以下方式：
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论