Date and time data types
Having a comprehensive suite of date and time types is necessary for effective time series data management, and ClickHouse delivers exactly that. From compact date representations to high-precision timestamps with nanosecond accuracy, these types are designed to balance storage efficiency with practical requirements for different time series applications.
Whether you're working with historical financial data, IoT sensor readings, or future-dated events, ClickHouse's date and time types provide the flexibility needed to handle various temporal data scenarios. The range of supported types allows you to optimize both storage space and query performance while maintaining the precision your use case demands.
-
The
Date
type should be sufficient in most cases. This type requires 2 bytes to store a date and limits the range to[1970-01-01, 2149-06-06]
. -
Date32
covers a wider range of dates. It requires 4 bytes to store a date and limits the range to[1900-01-01, 2299-12-31]
-
DateTime
stores date time values with second precision and a range of[1970-01-01 00:00:00, 2106-02-07 06:28:15]
It requires 4 bytes per value. -
For cases where more precision is required,
DateTime64
can be used. This allows storing time with up to nanoseconds precision, with a range of[1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]
. It requires 8 bytes per value.
Let's create a table that stores various date types:
We can use the now()
function to return the current time and now64()
to get it in a specified precision via the first argument.
This will populate our columns with time accordingly to the column type:
Timezones
Many use cases require having timezones stored as well. We can set the timezone as the last argument to the DateTime
or DateTime64
types:
Having defined a timezone in our DDL, we can now insert times using different timezones:
And now let's have a look what's in our table:
In the first row, we inserted all values using the America/New_York
timezone.
dt_1
anddt64_1
are automatically converted toEurope/Berlin
at query time.dt_2
anddt64_2
didn't have a time zone specified, so they use the server's local time zone, which in this case isEurope/London
.
In the second row, we inserted all the values without a timezone, so the server's local time zone was used.
As in the first row, dt_1
and dt_3
are converted to Europe/Berlin
, while dt_2
and dt64_2
use the server's local time zone.
Date and time functions
ClickHouse also comes with a set of functions that let us convert between the different data types.
For example, we can use toDate
to convert a DateTime
value to the Date
type:
We can use toDateTime64
to convert DateTime
to DateTime64
:
And we can use toDateTime
to go from Date
or DateTime64
back to DateTime
: