6 Nov 2023

ClickHouse: Convert date or datetime to epoch

I’ve been working with dates in ClickHouse today and I wanted to convert some values into epoch seconds/milliseconds to use with another tool. We’re going to document how to do that in this blog post, for future me if no one else.

Let’s start an instance of ClickHouse Local:

clickhouse local -m

And now we’ll write a query that returns the current date/time:

SELECT now() AS time;

Output

┌────────────────time─┐
│ 2023-11-06 14:58:19 │
└─────────────────────┘

If we want to convert this value to epoch seconds, we can use the toUnixTimestamp function.

SELECT toUnixTimestamp(now()) AS time

Output

┌───────time─┐
│ 1699282944 │
└────────────┘

There’s an equivalent function, toUnixTimestamp64Milli, that will give us epoch millis. Let’s try that:

SELECT toUnixTimestamp64Milli(now()) AS time;

Output

Received exception:
Code: 43. DB::Exception: The only argument for function toUnixTimestamp64Milli must be DateTime64: While processing toUnixTimestamp64Milli(now()) AS time. (ILLEGAL_TYPE_OF_ARGUMENT)

Hmmm, looks like we’ll need to do some casting.

SELECT toUnixTimestamp64Milli(cast(now(), 'DateTime64')) AS time;

Or we could use the toDateTime64 function

SELECT toUnixTimestamp64Milli(toDateTime64(now(), 3)) AS time; (1)

1	`3` means a granularity of `milliseconds`

Output

┌──────────time─┐
│ 1699283200000 │
└───────────────┘

If we genuinely do want the current time down to the millisecond, we’ll need to use the now64 function instead:

SELECT toUnixTimestamp64Milli(now64()) AS time;

Output

┌──────────time─┐
│ 1699283352267 │
└───────────────┘

How about if we only have a date, without a time? We can get that in epoch seconds like this:

select toUnixTimestamp(today()) AS date;

Output

┌───────date─┐
│ 1699228800 │
└────────────┘

If we want to get epoch millis, we need to convert the value to a DateTime64:

SELECT toUnixTimestamp64Milli(toDateTime64(today(), 3)) AS date;

Output

┌──────────date─┐
│ 1699228800000 │
└───────────────┘

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.