· clickhouse clickhouse-local til

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;
│ 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
│ 1699282944 │

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

SELECT toUnixTimestamp64Milli(now()) AS time;
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
│ 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;
│ 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;
│ 1699228800 │

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

SELECT toUnixTimestamp64Milli(toDateTime64(today(), 3)) AS date;
│ 1699228800000 │
  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket