ClickHouse DateTime64: Precision, Timezones, and Common Pitfalls

DateTime64 is ClickHouse's data type for timestamps with sub-second precision. Where plain DateTime stores whole seconds, DateTime64 stores a configurable number of fractional digits, from tenths of a second all the way down to nanoseconds. That flexibility comes with a handful of sharp edges around precision choice, timezone semantics, and arithmetic that catch most teams at least once. This guide covers how the type works internally, how to declare and use it correctly, and the pitfalls worth knowing before you commit a schema.

Declaration and Internal Representation

The syntax is DateTime64(precision, [timezone]):

CREATE TABLE events
(
    event_time DateTime64(3, 'UTC'),
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
ORDER BY (event_time, user_id);

Internally, a DateTime64 is stored as a single signed Int64 counting ticks since the Unix epoch (1970-01-01 00:00:00 UTC). The precision parameter sets the tick size to 10^-precision seconds. So DateTime64(3) ticks are milliseconds, DateTime64(6) are microseconds, and DateTime64(9) are nanoseconds. The value is always 8 bytes on disk regardless of the precision you pick — higher precision does not cost more raw storage, though it does change how compressible the column is (see below).

The timezone is the second, optional parameter. It is stored only in the column metadata, not in each row. It affects how string values are parsed on insert and how the value is rendered as text — it does not change the underlying UTC tick count. This is the single most important fact about timezones in ClickHouse, and the source of most confusion. See Timezone Semantics below.

The Precision Parameter (0-9)

precision accepts an integer from 0 to 9. A few reference points:

DateTime64(3) is the default-friendly choice for the vast majority of workloads — millisecond resolution is enough for application events, and it matches what most upstream systems (Kafka, JSON logs, JDBC) emit.

Pick the lowest precision that satisfies your requirements. Two reasons:

1. Compression. The low-order digits of a high-precision timestamp are effectively random noise. A DateTime64(9) column where the real source only has millisecond resolution stores six digits of zero-padding or jitter that compresses poorly compared to an honest DateTime64(3). Pairing the column with a CODEC(DoubleDelta, ZSTD) (see CREATE TABLE) helps, but you cannot recover information you never had.
2. The range ceiling. Higher precision shrinks the representable date range (next section).

Value Range and the Nanosecond Ceiling

The full supported range for DateTime64 is:

[1900-01-01 00:00:00, 2299-12-31 23:59:59.999999999]

That range holds at lower precisions. But because the underlying counter is a fixed 64-bit integer, higher precision means fewer representable seconds. At maximum precision of 9 (nanoseconds), an Int64 of ticks overflows much sooner, and the maximum representable value drops to:

2262-04-11 23:47:16 UTC

If you are timestamping anything that could legitimately carry a far-future date (expirations, scheduling, simulations) and you also need nanosecond precision, you can hit this wall. For almost all real workloads it is academic — but it is a real constraint, not a rounding artifact.

Inserting Values

How ClickHouse interprets an inserted literal depends on its form:

-- String: parsed using the COLUMN's timezone, then converted to UTC ticks
INSERT INTO events VALUES ('2024-06-01 12:00:00.123', 1, 'a');

-- Integer: treated as a scaled Unix timestamp (UTC) in tick units.
-- For DateTime64(3), this is milliseconds since the epoch.
INSERT INTO events VALUES (1717243200123, 2, 'b');

-- Decimal: the integer part is seconds (Unix timestamp, UTC),
-- the fractional part is the sub-second component.
INSERT INTO events VALUES (1717243200.123, 3, 'c');

Two gotchas worth internalizing:

• Integer literals are scaled. A bare integer inserted into DateTime64(3) is milliseconds, not seconds. Insert the same integer into DateTime64(6) and it is microseconds — the same number produces a different instant. Always know your column's precision when shipping raw integers.
• Strings are parsed in the column timezone. '2024-06-01 12:00:00.123' inserted into DateTime64(3, 'Europe/Berlin') means noon Berlin time; into DateTime64(3, 'UTC') it means noon UTC. The stored tick count differs by the offset.

To build a DateTime64 explicitly from a string in queries, use toDateTime64() with the precision:

SELECT toDateTime64('2024-06-01 12:00:00.123456', 6) AS ts;

Unlike DateTime, DateTime64 does not auto-convert from a string in every context — prefer the explicit toDateTime64() when the target type is ambiguous. For messy or mixed-format input, see parseDateTimeBestEffort and the Cannot parse DateTime error guide.

Timezone Semantics

Because the timezone lives in the column type and not the data, these rules follow:

• The stored value is always UTC ticks. DateTime64(3, 'America/New_York') and DateTime64(3, 'UTC') storing the same instant hold the same Int64 on disk; they only render differently.
• toTimeZone(ts, 'Asia/Tokyo') does not change the instant — it returns a value whose type carries a different display timezone. The wall-clock text changes; the moment in time does not.
• The session/server timezone matters for parsing string literals that target a timezone-less DateTime64(n). Check it with SELECT timezone().

-- Same instant, two renderings:
SELECT
    toDateTime64('2024-06-01 12:00:00', 3, 'UTC') AS utc,
    toTimeZone(utc, 'Asia/Tokyo')                 AS tokyo;
-- utc:   2024-06-01 12:00:00.000
-- tokyo: 2024-06-01 21:00:00.000   (same moment, +9h display)

The strongest defensive practice is to store everything in UTC and convert only at display time. Mixing timezones across insert paths — for example, a direct INSERT and a materialized view that re-parse the same string under different default timezones — silently produces different stored instants. DST transitions add a further trap: during the autumn "fall back" overlap, a local wall-clock time is ambiguous, and ClickHouse resolves it deterministically (it picks the earlier occurrence), which may not match what your upstream system chose.

Arithmetic Pitfalls

This is the pitfall that bites hardest. Do not do plain numeric arithmetic on a DateTime64 to add or subtract fractional seconds.

-- WRONG: looks reasonable, does the wrong thing
SELECT ts - 0.1            AS bad1,   -- often a no-op
       ts - toDecimal64(0.1, 3) AS bad2;   -- can collapse to 1970-01-01

Because the value is an integer tick count under the hood, subtracting a float or decimal does not mean "subtract 0.1 seconds" — it triggers type juggling that either gets ignored or corrupts the result. Use interval functions instead, which are unambiguous and fast:

-- CORRECT (ClickHouse 22.4+)
SELECT ts + toIntervalMillisecond(100) AS plus_100ms,
       ts - toIntervalMillisecond(100) AS minus_100ms,
       addMilliseconds(ts, 1250)        AS plus_1_25s,
       addMicroseconds(ts, 500),
       addNanoseconds(ts, 42);

On older versions without the interval helpers, round-trip through a scaled Unix timestamp:

SELECT fromUnixTimestamp64Milli(toUnixTimestamp64Milli(ts) + 1250) AS plus_1_25s;

The toUnixTimestamp64Milli / Micro / Nano and matching fromUnixTimestamp64* functions are also the canonical way to move between a DateTime64 and an integer when integrating with systems that speak epoch milliseconds. Interval-based arithmetic is not just correct — it is also faster than decimal reinterpretation in practice.

For truncation and bucketing (hour/day/minute rollups), use dateTrunc rather than hand-rolled math, and formatDateTime when you need formatted string output that preserves sub-second digits with the %f specifier.

Common Issues

1. Comparing across precisions. Comparing a DateTime64(3) value to a DateTime64(6) value, or to a plain DateTime, forces a conversion. ClickHouse handles it, but a literal compared against the column may be interpreted at the wrong scale. Cast both sides to the same type when in doubt.

2. Integer insert at the wrong scale. As noted above, an epoch-milliseconds integer dropped into a DateTime64(6) column lands 1000x off (treated as microseconds). Symptom: every row jumps to 1970.

3. Lost precision on the way in. If your source sends 2024-06-01 12:00:00.123456789 into a DateTime64(3) column, the value is truncated to milliseconds (.123). The extra digits are silently dropped, not rounded with carry. Match the column precision to the source.

4. Float/decimal arithmetic. Covered above — the most common single mistake. Always use interval functions.

5. Timezone drift between ingestion paths. Two writers parsing the same string under different default timezones store different instants. Standardize on UTC end to end.

Best Practices

1. Default to DateTime64(3, 'UTC') unless you have a concrete reason for more precision or a different display timezone. Millisecond UTC covers most event and log workloads cleanly.
2. Store in UTC, display on read. Keep the column timezone-neutral (or explicitly 'UTC') and apply toTimeZone() only in the SELECT that renders to a user.
3. Pick the lowest sufficient precision. It compresses better and keeps the date range wide. Do not store nanoseconds you do not have.
4. Use interval functions for all sub-second math. toIntervalMillisecond, addMilliseconds, addMicroseconds, addNanoseconds — never bare + 0.1.
5. Add a time codec. CODEC(DoubleDelta, ZSTD) on the timestamp column typically pays for itself on time-ordered data. See CREATE TABLE.
6. Make the timestamp the leading sort key for time-range queries, when that matches your access pattern — ORDER BY (event_time, ...).

How Pulse Helps

DateTime64 problems are insidious because they rarely throw errors — they quietly store the wrong instant or silently truncate precision, and you only notice when a dashboard is off by hours or a join misses rows. Pulse continuously profiles ClickHouse schemas and query patterns, and surfaces timestamp-related risks: columns where the chosen precision does not match the data actually being written, timestamp columns missing an effective compression codec, timezone inconsistencies between ingestion paths, and queries doing expensive per-row datetime conversions that should be pushed into the schema. Because the costly DateTime64 decisions (precision and timezone) are baked into the column type and painful to change on a large table, catching them early matters. Connect your ClickHouse cluster to Pulse to keep an eye on timestamp and schema health automatically.

Frequently Asked Questions

Q: What is the difference between DateTime and DateTime64?

DateTime stores whole seconds in 4 bytes; DateTime64(n) stores sub-second precision (n fractional digits, 0-9) in 8 bytes as an Int64 tick count. Use DateTime64 when you need millisecond or finer resolution.

Q: Which precision should I use?

Use the lowest that meets your needs. DateTime64(3) (milliseconds) is the common default. Choose 6 (microseconds) or 9 (nanoseconds) only for genuinely high-resolution sources, and remember that precision 9 caps the maximum date at 2262-04-11.

Q: Why does subtracting 0.1 from a DateTime64 give a wrong result?

Because the value is an integer tick count, not a float. Plain numeric arithmetic does not subtract 0.1 seconds — it either no-ops or collapses to 1970. Use ts - toIntervalMillisecond(100) or addMilliseconds(ts, -100) instead.

Q: Does the timezone change the stored value?

No. The stored Int64 is always UTC ticks. The timezone lives in the column metadata and only affects how strings are parsed on insert and how values are rendered as text. toTimeZone() changes the display, not the instant.

Q: How do I convert a DateTime64 to and from epoch milliseconds?

Use toUnixTimestamp64Milli(ts) to get an integer of milliseconds since the epoch, and fromUnixTimestamp64Milli(n) to go back. There are matching Micro and Nano variants for higher precision.

Q: What happens if my source has more digits than the column precision?

The extra digits are truncated, not rounded with carry. Inserting .123456 into a DateTime64(3) column stores .123. Match the column precision to your source to avoid silent precision loss.