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:

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.

Examples

Filter on DateTime values

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

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:

┌─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.

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

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().

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

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.

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:

┌──────────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.

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

    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.

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

    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

Updated