Improved Timezone Support in InfluxDB 3.0

By Jeffrey Smith / Developer,
Nov 12, 2024

We have added a new function, tz, to InfluxDB 3.0 SQL to improve the ergonomics of working with timezones. This blog post will provide more details on how to use this function and when it makes sense to use it instead of the existing SQL timezone functions.

tz function

The goal of the new tz function is to consistently streamline conversion between various time zones, for points in time relative to the Unix epoch. This is in contrast to the at time zone syntax, which provides different semantics depending on the input data. See the “What is Time” section for a more detailed explanation of the difference between the two.

Usage

The tz function takes one or two arguments. The first argument is always a Nanosecond Timestamp, with any timezone or the absence of a timezone. The second argument is a valid timezone string. If not provided, the default is UTC.

Some examples of how to use tz:

-- Convert from un-timezoned to 'Europe/Brussels'
SELECT tz('2024-04-01T00:00:20', 'Europe/Brussels') as t;
+---------------------------+
| t                         |
+---------------------------+
| 2024-04-01T02:00:20+02:00 |
+---------------------------+

-- Convert from un-timezoned to UTC
SELECT tz('2024-04-01T00:00:20') as t, arrow_typeof(tz('2024-04-01T00:00:20')) as type;
+----------------------+------------------------------------+
| t                    | type                               |
+----------------------+------------------------------------+
| 2024-04-01T00:00:20Z | Timestamp(Nanosecond, Some("UTC")) |
+----------------------+------------------------------------+

-- Convert from un-timezoned to America/New_York and then to America/Denver
SELECT tz(tz('2024-04-01T00:00:20', 'America/New_York'), 'America/Denver') as t;
+---------------------------+
| t                         |
+---------------------------+
| 2024-03-31T18:00:20-06:00 |
+---------------------------+

One thing to note is that the return type of tz will always have a timezone associated with it, even when that timezone is UTC. This matters for languages like Python (and others), where comparing tz-naive and tz-aware types is not supported. Any client code that utilizes this functionality will need to be ready to handle timezone-aware data.

SELECT arrow_typeof(tz('2024-04-01T00:00:20'));
+-----------------------------------------------+
| arrow_typeof(tz(Utf8("2024-04-01T00:00:20"))) |
+-----------------------------------------------+
| Timestamp(Nanosecond, Some("UTC"))            |
+-----------------------------------------------+

What is time

To explain this change, it is useful to think of a given time as either “absolute” or “wall clock.”

An absolute time is a time considered relative to the UTC epoch. For example, the time 2024-08-01T06:00:00Z is 1722492000 epoch seconds which is equivalent to 2024-08-01T00:00:00-06:00 (America/Denver timezone).

A wall clock time is the time that you would actually see on a clock in a given timezone. For example, you might want the time 2024-07-17T17:00:00 as it was in America/Denver at that moment, so your time would be represented by 2024-07-17T17:00:00-06:00 (1721257200 epoch seconds).

The difference between these two ideas is exemplified by tz vs. at time zone.

Let’s look at a simple example:

SELECT '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels';
+------------------------------+
| Utf8("2024-04-01T00:00:20Z") |
+------------------------------+
| 2024-04-01T00:00:20+02:00    |
+------------------------------+

SELECT tz('2024-04-01T00:00:20', 'Europe/Brussels');
+---------------------------------------------------------+
| tz(Utf8("2024-04-01T00:00:20"),Utf8("Europe/Brussels")) |
+---------------------------------------------------------+
| 2024-04-01T02:00:20+02:00                               |
+---------------------------------------------------------+

When a timestamp does not have a timezone (the default behavior in InfluxDB 3.0), the at time zone cast returns the wall clock time in the specified time zone. This function can be useful when you are setting time bounds in a “where” clause and you want them to be based on a specific time zone (often your local time).

For example, you want to restrict a query to the last day with the days starting on the America/New_York boundaries:

select * from my_cool_stuff where time > '2024-01-01' at time zone 'America/New_York' and time < '2024-01-02' at time zone 'America/New_York'

The tz function always returns the absolute time in the specified time zone. This is useful when you have existing data that was written into InfluxDB (which is always UTC per the line protocol spec), and you want to display it at the same point in time, but in a local time zone.

For example, you have Telegraf writing your memory usage to a database. Rather than having to mentally apply the offset to every value, you can use the tz function:

select
    time,
    time at time zone 'America/New_York' as time_atz,
    tz(time, 'America/New_York') as time_tz 
from mem order by time limit 3;
+----------------------+---------------------------+---------------------------+
| time                 | time_atz                  | time_tz                   |
+----------------------+---------------------------+---------------------------+
| 2020-06-11T16:51:50Z | 2020-06-11T16:51:50-04:00 | 2020-06-11T12:51:50-04:00 |
| 2020-06-11T16:52:00Z | 2020-06-11T16:52:00-04:00 | 2020-06-11T12:52:00-04:00 |
| 2020-06-11T16:52:10Z | 2020-06-11T16:52:10-04:00 | 2020-06-11T12:52:10-04:00 |
+----------------------+---------------------------+---------------------------+

In the example above, time_atz is not an accurate representation of when the data was written. It has effectively shifted the points 4 hours into the future. time_tz provides the desired behavior, seeing the fixed point in time that a point was recorded but displayed in a different time zone.