CODEWITHSUNDEEP
sqldatetimetimeclickhouseclickhouse-client
ditrauth
ditrauth

Reputation: 111

How convert "2022-10-26T00:00:00.654199+00:00" timeformat to unix

I need convert string 2022-10-26T00:00:00.654199+00:00 to Unix timestamp, is it possible with clickhouse?

    toUnixTimestamp64Milli(visitParamExtractString(msg, 'time'))

where time is 2022-10-26T00:00:00.654199+00:00

This way it doesn't work.

Upvotes: 0

Views: 224

Answers (1)

vladimir
vladimir

Reputation: 15178

Try this way:

SELECT
    json,
    JSONExtractString(json, 'time') AS time,
    parseDateTime64BestEffort(time, 6) AS dt,
    toUnixTimestamp64Milli(dt) AS ts
FROM 
(
    WITH [
        '{"time": "2022-10-26T00:00:00.654199+00:00"}',
        '{"time": "2022-10-26T00:00:00.654199+08:00"}'] AS jsons
    SELECT arrayJoin(jsons) AS json
)

/*
┌─json─────────────────────────────────────────┬─time─────────────────────────────┬─────────────────────────dt─┬────────────ts─┐
│ {"time": "2022-10-26T00:00:00.654199+00:00"} │ 2022-10-26T00:00:00.654199+00:00 │ 2022-10-26 00:00:00.654199 │ 1666742400654 │
│ {"time": "2022-10-26T00:00:00.654199+08:00"} │ 2022-10-26T00:00:00.654199+08:00 │ 2022-10-25 16:00:00.654199 │ 1666713600654 │
└──────────────────────────────────────────────┴──────────────────────────────────┴────────────────────────────┴───────────────┘
*/

Upvotes: 1

Related Questions