How to cast date Strings to DateTime format with extended parsing in ClickHouse?

Question

I have a String field with timestamp like this: "2020-01-13T07:34:25.804445Z". And i want to parse it to datetime (to use in Grafana filters, for example). But i getting this error:

SELECT SELECT "@timestamp" AS timestamp, CAST(timestamp AS DateTime) as datetime from table

Cannot parse string '2020-01-13T06:55:05.704Z' as DateTime: syntax error at position 19 (parsed just '2020-01-13T06:55:05').

I found variable date_time_input_format on documentation which "allows extended parsing". But it says that this setting doesn't apply to date and time functions. So how do i cast string date with timezone to DateTime?

Actually, this "date_time_input_format" allows me to declare fields ad DateTime and add my "extended" timestamps into it directly without casting. — DenisNovac, Jan 13 '20 at 10:23

score 17 · Accepted Answer · answered Jan 13 '20 at 15:17

SELECT parseDateTimeBestEffortOrNull('2020-01-13T07:34:25.804445Z')

┌─parseDateTimeBestEffortOrNull('2020-01-13T07:34:25.804445Z')─┐
│                                          2020-01-13 07:34:25 │
└──────────────────────────────────────────────────────────────┘

https://clickhouse.yandex/docs/en/query_language/functions/type_conversion_functions/#type_conversion_functions-parsedatetimebesteffort

How to cast date Strings to DateTime format with extended parsing in ClickHouse?

1 Answers1