---
title: DateTime Data Type reference
meta:
    description: Documentation for the DateTime data type.
---

# DateTime

The `DateTime` data type stores a specific point in time, including both a calendar date and a time of day, with second-level precision.

Syntax:

```sql
DateTime([timezone])
```

Supported range of values: `[1970-01-01 00:00:00, 2106-02-07 06:28:15]`.

Resolution: 1 second.

## Speed

The `Date` data type generally offers better performance than `DateTime` under most conditions.

`Date` values require 2 bytes of storage, while `DateTime` values require 4 bytes. This difference in storage size can be amplified during data compression, as the minute and second components of `DateTime` values are typically less compressible. Consequently, filtering and aggregating data using `Date` types can be faster compared to `DateTime`.

## Usage Remarks

A `DateTime` value internally stores a Unix timestamp, which represents the number of seconds since `1970-01-01 00:00:00 UTC`, regardless of any specified timezone. The timezone associated with a `DateTime` column primarily influences how these internal timestamps are displayed in text format and how string literals are parsed into `DateTime` values.

The timezone information is associated with the column definition, not stored with each individual value in the table. When a timezone is not explicitly specified for a `DateTime` column, Tinybird uses a default timezone, which is typically UTC.

You can find a list of supported timezones in the [IANA Time Zone Database](https://www.iana.org/time-zones).

## Examples

### Filter on `DateTime` values

You can filter `DateTime` values by explicitly converting a string to `DateTime` with a specified timezone.

```sql
SELECT toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul') AS timestamp_value
WHERE timestamp_value = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')
```

Result:

```result
┌─timestamp_value───┐
│ 2019-01-01 00:00:00 │
└───────────────────┘
```

`DateTime` values can also be filtered using a string literal directly in a `WHERE` clause. The string will be automatically converted to `DateTime` using the column's timezone.

```sql
SELECT toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul') AS timestamp_value
WHERE timestamp_value = '2019-01-01 00:00:00'
```

Result:

```result
┌─timestamp_value───┐
│ 2019-01-01 00:00:00 │
└───────────────────┘
```

### Get a time zone for a `DateTime`-type column

You can inspect the type of a `DateTime` value, which includes its associated timezone, using `toTypeName()`.

```sql
SELECT toDateTime(now(), 'Asia/Istanbul') AS current_time, toTypeName(current_time) AS type_name
```

Result:

```result
┌──────────current_time─┬─type_name─────────────────┐
│ 2023-10-27 10:30:00   │ DateTime('Asia/Istanbul') │
└───────────────────────┴───────────────────────────┘
```

### Timezone conversion

To display a `DateTime` value in a different timezone, you can use `toDateTime()` with the desired timezone. This operation only changes how the timestamp is interpreted and displayed, not the underlying stored Unix timestamp.

```sql
SELECT
    toDateTime('2023-10-27 10:30:00', 'UTC') AS utc_time,
    toDateTime(utc_time, 'Europe/London') AS london_time,
    toDateTime(utc_time, 'Asia/Istanbul') AS istanbul_time
```

Result:

```result
┌──────────utc_time───┬──────────london_time──┬────────istanbul_time──┐
│ 2023-10-27 10:30:00 │ 2023-10-27 11:30:00  │ 2023-10-27 13:30:00   │
└─────────────────────┴──────────────────────┴───────────────────────┘
```

## Limitations on Time Zone Support

While `DateTime` supports a wide range of timezones, some historical or unusual timezone definitions might lead to minor inaccuracies in calculations:

*   **Non-standard UTC offsets**: Timezones with offsets that are not multiples of 15 minutes (e.g., UTC -0:44:30) might cause slight discrepancies in hour and minute calculations for historical dates.
*   **Unusual time transitions**: Time transitions (like Daylight Saving Time changes) that occur at non-standard intervals (e.g., not on the hour or half-hour) could result in minor inaccuracies for specific dates.
*   **Non-monotonic calendar dates**: In rare cases where time literally moves backward on the calendar (e.g., a day repeating an hour), `DateTime` calculations might not perfectly reflect these complex historical events.

For critical applications involving highly precise historical time calculations in unusual timezones, it's often recommended to store and perform calculations in UTC to avoid potential ambiguities.

## Handling Daylight Saving Time (DST)

The `DateTime` type can exhibit specific behaviors during Daylight Saving Time (DST) transitions, especially when performing arithmetic operations or parsing ambiguous times.

*   **Overlapping times (Fall Back)**: When clocks move backward (e.g., from DST to standard time), an hour might occur twice. By default, `DateTime` typically interprets such ambiguous times as the *earlier* occurrence. Adding time intervals across this transition might produce unexpected results if not handled carefully.

    ```sql
    SELECT
        toDateTime('2023-10-29 01:30:00', 'Europe/London') AS time_before_transition,
        time_before_transition + toIntervalHour(1) AS one_hour_later
    ```

    Result:

    ```result
    ┌─time_before_transition─┬─one_hour_later─────┐
    │ 2023-10-29 01:30:00    │ 2023-10-29 01:30:00 │
    └────────────────────────┴────────────────────┘
    ```
    *(Note: In this example, 01:30:00 occurs twice. Adding an hour to the first occurrence (BST) results in the second occurrence (GMT), which is still 01:30:00 local time.)*

*   **Skipped times (Spring Forward)**: When clocks move forward (e.g., from standard time to DST), an hour might be skipped. If you attempt to parse or calculate a time that falls within this non-existent interval, `DateTime` might adjust it to the nearest valid time, often by shifting it backward or forward.

    ```sql
    SELECT
        toDateTime('2023-03-26 01:30:00', 'Europe/London') AS non_existent_time,
        non_existent_time + toIntervalHour(1) AS one_hour_later
    ```

    Result:

    ```result
    ┌─non_existent_time───┬─one_hour_later────┐
    │ 2023-03-26 00:30:00 │ 2023-03-26 02:30:00 │
    └─────────────────────┴───────────────────┘
    ```
    *(Note: The time `2023-03-26 01:30:00` did not exist in London. The system adjusted it to `00:30:00` and then added an hour, resulting in `02:30:00`.)*

When working with `DateTime` values across DST transitions, especially for precise calculations, consider performing operations in UTC and converting to local timezones only for display purposes.

## See Also

*   [Type conversion functions](../functions/type-conversion-functions)
*   [Date and time functions](../functions/date-time-functions)
*   [Array functions](../functions/array-functions)
*   `date_time_input_format` setting
*   `date_time_output_format` setting
*   `timezone` server configuration parameter
*   `session_timezone` setting
*   Operators for working with dates and times
*   [Date data type](../data-types/date)