DateTime64

The DateTime64 data type stores a specific point in time, including both date and time components, with fractional second precision. It allows you to define the exact level of sub-second precision required for your timestamp data.

Syntax

DateTime64(precision, [timezone])

Arguments

  • precision: An integer from 0 to 9, indicating the number of decimal places for sub-second precision. For example, 3 for milliseconds, 6 for microseconds, or 9 for nanoseconds.
  • timezone: (Optional) A string representing the IANA time zone name (e.g., 'America/New_York', 'Europe/London'). If omitted, the server's default time zone is used. This time zone affects how string values are parsed and how the DateTime64 value is displayed.

Description

Internally, DateTime64 values are stored as a 64-bit integer representing the number of "ticks" since the Unix epoch (1970-01-01 00:00:00 UTC). The precision parameter determines the duration of each tick. The specified timezone is stored in the column's metadata, influencing how string inputs are interpreted and how the value is formatted for display, but it does not alter the underlying UTC timestamp.

The supported range for DateTime64 values is from 1900-01-01 00:00:00 to 2299-12-31 23:59:59.99999999. Note that if the maximum precision of 9 digits (nanoseconds) is used, the maximum supported value is 2262-04-11 23:47:16 in UTC.

Examples

Defining a DateTime64 column in a Data Source

To use DateTime64 in Tinybird, you define it as a column type in your Data Source schema. The timezone specified in the schema will be used for parsing incoming string data and for displaying the timestamp.

dt64.datasource
SCHEMA >
    timestamp DateTime64(3, 'Asia/Istanbul') `json:$.ts`
    event_id UInt8 `json:$.event_id`

ENGINE "MergeTree"

You can then ingest data into this Data Source. When ingesting, integer values are treated as Unix timestamps (in UTC) scaled by the precision, while string values are parsed according to the column's defined timezone.

tb push datasources/dt64.datasource

echo '{"ts":1546300800123,"event_id":1}' > dt64.ndjson
echo '{"ts":1546300800.123,"event_id":2}' >> dt64.ndjson
echo '{"ts":"2019-01-01 00:00:00","event_id":3}' >> dt64.ndjson

tb datasource append dt64 dt64.ndjson

Querying DateTime64 values

Once data is ingested, you can query it using standard SQL SELECT statements.

SELECT * FROM dt64

Result:

┌──────────────────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000          │        3 │
│ 2019-01-01 03:00:00.123          │        1 │
│ 2019-01-01 03:00:00.123          │        2 │
└──────────────────────────────────┴──────────┘

Filtering DateTime64 values

You can filter DateTime64 columns using comparison operators. It's often best to use the toDateTime64 function to ensure the literal value you're comparing against has the correct precision and timezone.

SELECT * FROM dt64 WHERE timestamp = toDateTime64('2019-01-01 03:00:00', 3, 'Asia/Istanbul')

Result:

┌──────────────────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000          │        3 │
└──────────────────────────────────┴──────────┘

SELECT * FROM dt64 WHERE timestamp = toDateTime64(1546300800.123, 3)

Result:

┌──────────────────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.123          │        1 │
│ 2019-01-01 03:00:00.123          │        2 │
└──────────────────────────────────┴──────────┘

Inspecting DateTime64 type information

You can use toTypeName to see the full type definition, including precision and timezone.

SELECT toDateTime64('2024-01-01 12:34:56.789', 3, 'Asia/Istanbul') AS current_time, toTypeName(current_time) AS type_info

Result:

┌──────────────────────────current_time─┬─type_info──────────────────────┐
│ 2024-01-01 12:34:56.789            │ DateTime64(3, 'Asia/Istanbul') │
└──────────────────────────────────┴────────────────────────────────┘

Timezone conversion

You can convert a DateTime64 value to display in a different timezone by using toDateTime64 with the desired timezone. This reinterprets the underlying UTC timestamp for display in the new timezone.

SELECT
    timestamp,
    toDateTime64(timestamp, 3, 'Europe/London') AS london_time,
    toDateTime64(timestamp, 3, 'Asia/Istanbul') AS istanbul_time
FROM dt64

Result:

┌──────────────────────────timestamp─┬────────────────london_time─┬────────────────istanbul_time─┐
│ 2019-01-01 03:00:00.000          │ 2019-01-01 00:00:00.000 │ 2019-01-01 03:00:00.000 │
│ 2019-01-01 03:00:00.123          │ 2019-01-01 00:00:00.123 │ 2019-01-01 03:00:00.123 │
│ 2019-01-01 03:00:00.123          │ 2019-01-01 00:00:00.123 │ 2019-01-01 03:00:00.123 │
└──────────────────────────────────┴───────────────────────────┴──────────────────────────────┘
Updated