Skip to content
Coordinator DB
View as Markdown
View as Markdown

DateTime64 ​

DateTime64 represents an instant with configurable fractional-second precision and a declared timezone. Use it for event times that require milliseconds, microseconds or nanoseconds.

Syntax ​

text
DateTime64
DateTime64(P)
DateTime64(P, 'timezone')
TIMESTAMP
TIMESTAMP(P)
TIMESTAMP(P, 'timezone')

Parameters and range ​

  • P — The number of fractional-second digits, from 0 through 9. DateTime64 defaults to 3; the TIMESTAMP alias defaults to 6.
  • timezone — An IANA timezone name. The default is 'UTC'.

The calendar range is 1900 through 2299, further limited by signed 64-bit tick capacity. Precision 9 reaches its upper tick limit in 2262.

Input units ​

Integer input is a count of ticks at the declared precision. At precision 3, 1234 means 1.234 seconds after the Unix epoch. Decimal input instead expresses epoch seconds. Text is interpreted as local date and time in the declared timezone.

Casting to a numeric type returns epoch seconds; a REAL or Decimal target can retain fractions. Lowering precision discards fractional digits while remaining in the containing tick, including for times before 1970.

As with DateTime, ambiguous or nonexistent local times are rejected. Text with no clock time denotes local midnight.

Examples ​

Preserving microseconds ​

sql
SELECT CAST('2025-03-05 12:30:00.123456' AS DateTime64(6, 'UTC'))
FORMAT csv;
text
"2025-03-05 12:30:00.123456"

Integer ticks and decimal seconds ​

Both values below represent the same instant:

sql
SELECT CAST(1234 AS DateTime64(3, 'UTC')),
       CAST(CAST('1.234' AS Decimal(18, 3)) AS DateTime64(3, 'UTC'))
FORMAT csv;
text
"1970-01-01 00:00:01.234","1970-01-01 00:00:01.234"

Reducing precision ​

sql
SELECT CAST(
    CAST('2025-03-05 12:30:00.123456' AS DateTime64(6))
    AS DateTime64(3)
)
FORMAT csv;
text
"2025-03-05 12:30:00.123"