---
title: DateTime64 Data Type reference
meta:
    description: "Documentation for the DateTime64 data type, which stores timestamps with sub-second precision."
headingMaxLevels: 2
---

# 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

```sql
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.

```tb {% title="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.

```bash
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.

```sql
SELECT * FROM dt64
```

Result:

```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.

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

Result:

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

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

Result:

```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.

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

Result:

```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.

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

Result:

```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 │
└──────────────────────────────────┴───────────────────────────┴──────────────────────────────┘
```