> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Date and time data types - time-series

> Time-series data types in ClickHouse.

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`](/reference/data-types/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`](/reference/data-types/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`](/reference/data-types/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`](/reference/data-types/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:

```sql theme={null}
CREATE TABLE dates
(
    `date` Date,
    `wider_date` Date32,
    `datetime` DateTime,
    `precise_datetime` DateTime64(3),
    `very_precise_datetime` DateTime64(9)
)
ENGINE = MergeTree
ORDER BY tuple();
```

We can use the [`now()`](/reference/functions/regular-functions/date-time-functions#now) function to return the current time and [`now64()`](/reference/functions/regular-functions/date-time-functions#now64) to get it in a specified precision via the first argument.

```sql theme={null}
INSERT INTO dates 
SELECT now(), 
       now()::Date32 + toIntervalYear(100),
       now(), 
       now64(3), 
       now64(9) + toIntervalYear(200);
```

This will populate our columns with time accordingly to the column type:

```sql theme={null}
SELECT * FROM dates
FORMAT Vertical;
```

```text theme={null}
Row 1:
──────
date:                  2025-03-12
wider_date:            2125-03-12
datetime:              2025-03-12 11:39:07
precise_datetime:      2025-03-12 11:39:07.196
very_precise_datetime: 2025-03-12 11:39:07.196724000
```

<h2 id="time-series-time-types">
  Time and Time64 types
</h2>

For scenarios where you need to store time-of-day values without date components, ClickHouse provides the [`Time`](/reference/data-types/time) and [`Time64`](/reference/data-types/time64) types, which was introduced in version 25.6. These are useful for representing recurring schedules, daily patterns, or situations where separating date and time components makes sense.

<Note>
  Using `Time` and `Time64` requires enabling the setting: `SET enable_time_time64_type = 1;`

  These types were introduced in version 25.6
</Note>

The `Time` type stores hours, minutes, and seconds with second precision. Internally stored as a signed 32-bit integer, it supports a range of `[-999:59:59, 999:59:59]`, allowing for values that exceed 24 hours. This can be useful when tracking elapsed time or performing arithmetic operations that result in values outside a single day.

For sub-second precision, `Time64` stores time with configurable fractional seconds as a signed Decimal64 value. It accepts a precision parameter (0-9) to define the number of fractional digits. Common precision values are 3 (milliseconds), 6 (microseconds), and 9 (nanoseconds).

Neither `Time` nor `Time64` support timezones - they represent pure time-of-day values without regional context.

Let's create a table with time columns:

```sql theme={null}
SET enable_time_time64_type = 1;

CREATE TABLE time_examples
(
    `event_id` UInt8,
    `basic_time` Time,
    `precise_time` Time64(3)
)
ENGINE = MergeTree
ORDER BY event_id;
```

We can insert time values using string literals or numeric values. For `Time`, numeric values are interpreted as seconds since 00:00:00. For `Time64`, numeric values are interpreted as seconds since 00:00:00 with the fractional part interpreted according to the column's precision:

```sql theme={null}
INSERT INTO time_examples VALUES 
    (1, '14:30:25', '14:30:25.123'),
    (2, 52225, 52225.456),
    (3, '26:11:10', '26:11:10.789');  -- Values normalize beyond 24 hours

SELECT * FROM time_examples ORDER BY event_id;
```

```text theme={null}
┌─event_id─┬─basic_time─┬─precise_time─┐
│        1 │ 14:30:25   │ 14:30:25.123 │
│        2 │ 14:30:25   │ 14:30:25.456 │
│        3 │ 26:11:10   │ 26:11:10.789 │
└──────────┴────────────┴──────────────┘
```

Time values can be filtered naturally:

```sql theme={null}
SELECT * FROM time_examples WHERE basic_time = '14:30:25';
```

<h2 id="time-series-timezones">
  Timezones
</h2>

Many use cases require having timezones stored as well. We can set the timezone as the last argument to the `DateTime` or `DateTime64` types:

```sql theme={null}
CREATE TABLE dtz
(
    `id` Int8,
    `dt_1` DateTime('Europe/Berlin'),
    `dt_2` DateTime,
    `dt64_1` DateTime64(9, 'Europe/Berlin'),
    `dt64_2` DateTime64(9)
)
ENGINE = MergeTree
ORDER BY id;
```

Having defined a timezone in our DDL, we can now insert times using different timezones:

```sql theme={null}
INSERT INTO dtz 
SELECT 1, 
       toDateTime('2022-12-12 12:13:14', 'America/New_York'),
       toDateTime('2022-12-12 12:13:14', 'America/New_York'),
       toDateTime64('2022-12-12 12:13:14.123456789', 9, 'America/New_York'),
       toDateTime64('2022-12-12 12:13:14.123456789', 9, 'America/New_York')
UNION ALL
SELECT 2, 
       toDateTime('2022-12-12 12:13:15'),
       toDateTime('2022-12-12 12:13:15'),
       toDateTime64('2022-12-12 12:13:15.123456789', 9),
       toDateTime64('2022-12-12 12:13:15.123456789', 9);
```

And now let's have a look what's in our table:

```sql theme={null}
SELECT dt_1, dt64_1, dt_2, dt64_2
FROM dtz
FORMAT Vertical;
```

```text theme={null}
Row 1:
──────
dt_1:   2022-12-12 18:13:14
dt64_1: 2022-12-12 18:13:14.123456789
dt_2:   2022-12-12 17:13:14
dt64_2: 2022-12-12 17:13:14.123456789

Row 2:
──────
dt_1:   2022-12-12 13:13:15
dt64_1: 2022-12-12 13:13:15.123456789
dt_2:   2022-12-12 12:13:15
dt64_2: 2022-12-12 12:13:15.123456789
```

In the first row, we inserted all values using the `America/New_York` timezone.

* `dt_1` and `dt64_1` are automatically converted to `Europe/Berlin` at query time.
* `dt_2` and `dt64_2` didn't have a time zone specified, so they use the server's local time zone, which in this case is `Europe/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 `dt64_1` are converted to `Europe/Berlin`, while `dt_2` and `dt64_2` use the server's local time zone.

<h2 id="time-series-date-time-functions">
  Date and time functions
</h2>

ClickHouse also comes with a set of functions that let us convert between the different data types.

For example, we can use [`toDate`](/reference/functions/regular-functions/type-conversion-functions#toDate) to convert a `DateTime` value to the `Date` type:

```sql theme={null}
SELECT
    now() AS current_time,
    toTypeName(current_time),
    toDate(current_time) AS date_only,
    toTypeName(date_only)
FORMAT Vertical;    
```

```text theme={null}
Row 1:
──────
current_time:             2025-03-12 12:32:54
toTypeName(current_time): DateTime
date_only:                2025-03-12
toTypeName(date_only):    Date
```

We can use [`toDateTime64`](/reference/functions/regular-functions/type-conversion-functions#toDateTime64) to convert `DateTime` to `DateTime64`:

```sql theme={null}
SELECT
    now() AS current_time,
    toTypeName(current_time),
    toDateTime64(current_time, 3) AS date_only,
    toTypeName(date_only)
FORMAT Vertical;
```

```text theme={null}
Row 1:
──────
current_time:             2025-03-12 12:35:01
toTypeName(current_time): DateTime
date_only:                2025-03-12 12:35:01.000
toTypeName(date_only):    DateTime64(3)
```

And we can use [`toDateTime`](/reference/functions/regular-functions/type-conversion-functions#toDateTime) to go from `Date` or `DateTime64` back to `DateTime`:

```sql theme={null}
SELECT
    now64() AS current_time,
    toTypeName(current_time),
    toDateTime(current_time) AS date_time1,
    toTypeName(date_time1),
    today() AS current_date,
    toTypeName(current_date),
    toDateTime(current_date) AS date_time2,
    toTypeName(date_time2)
FORMAT Vertical;
```

```text theme={null}
Row 1:
──────
current_time:             2025-03-12 12:41:00.598
toTypeName(current_time): DateTime64(3)
date_time1:               2025-03-12 12:41:00
toTypeName(date_time1):   DateTime
current_date:             2025-03-12
toTypeName(current_date): Date
date_time2:               2025-03-12 00:00:00
toTypeName(date_time2):   DateTime
```