Timestamps and time zones
When working with timestamped data, it may be necessary to convert timestamp values to or from UTC, or to offset timestamp values by a fixed duration. The following sections describe how QuestDB handles timestamps natively, how to use built-in functions for working with time zone conversions, and general hints for working with time zones in QuestDB.
Timestamps in QuestDB​
The native timestamp format used by QuestDB is a Unix timestamp in microsecond resolution. Although timestamps in nanoseconds will be parsed, the output will be truncated to microseconds. QuestDB does not store time zone information alongside timestamp values and therefore it should be assumed that all timestamps are in UTC.
The following example shows how a Unix timestamp in microseconds may be passed into a timestamp column directly:
CREATE TABLE my_table (ts timestamp, col1 int) timestamp(ts);
INSERT INTO my_table VALUES(1623167145123456, 12);
my_table;
| ts | col1 |
|---|---|
| 2021-06-08T15:45:45.123456Z | 12 |
Timestamps may also be inserted as strings in the following way:
INSERT INTO my_table VALUES('2021-06-08T16:45:45.123456Z', 13);
my_table;
| ts | col1 |
|---|---|
| 2021-06-08T15:45:45.123456Z | 12 |
| 2021-06-08T16:45:45.123456Z | 13 |
When inserting timestamps into a table, it is also possible to use timestamp units to define the timestamp format, in order to process trailing zeros in exported data sources such as PostgreSQL:
INSERT INTO my_table VALUES(to_timestamp('2021-06-09T16:45:46.123456789', 'yyyy-MM-ddTHH:mm:ss.N+'), 14);
-- Passing 9-digit nanosecond into QuestDB, this is equal to:
INSERT INTO my_table VALUES(to_timestamp('2021-06-10T16:45:46.123456789', 'yyyy-MM-ddTHH:mm:ss.SSSUUUN'), 14);
my_table;
The output maintains microsecond resolution:
| ts | col1 |
|---|---|
| 2021-06-08T15:45:45.123456Z | 12 |
| 2021-06-08T16:45:45.123456Z | 13 |
| 2021-06-09T16:45:46.123456Z | 14 |
QuestDB's internal time zone database​
In order to simplify working with time zones, QuestDB uses the tz time zone database which is standard in the Java ecosystem. This time zone database is used internally in time zone lookup and in operations relating to timestamp value conversion to and from time zones.
For this reason, a time zone may be referenced by abbreviated name, by full time zone name or by UTC offset:
| Abbreviation | Time zone name | UTC offset |
|---|---|---|
| EST | America/New_York | -05:00 |
Referring to time zones​
It's strongly advised not to use the three-letter ID or abbreviation for time zones for the following reason:
The same abbreviation is often used for multiple time zones (for example, "CST" could be U.S. "Central Standard Time" and "China Standard Time"), and the Java platform can then only recognize one of them
Therefore, choosing a geographic region which observes a time zone
("America/New_York", "Europe/Prague") or a UTC offset value ("+02:00") is
more reliable when referring to time zones. Instructions for converting to and
from time zones are described in the
Converting timestamps to and from time zones
section below.
The current QuestDB time zone database uses the English locale but support
for additional locales may be added in future. Referring to time zones which are
outdated or not recognized results in a invalid timezone name error. The
following resources may be used for hints how to refer to time zones by ID or
offset:
- The official list maintained by IANA
- Java's getAvailableZoneIds method
- Wiki entry on tz database time zones (this is a convenient reference, but may not be 100% accurate)
Users should be aware that the time zone database contains both current and historic transitions for various time zones. Therefore time zone conversions must take the historic time zone transitions into account based on the timestamp values.
Updates to the time zone database​
The upstream project updates past time zones as new information becomes available. These changes are typically related to daylight saving time (DST) start and end date transitions and, on rare occasions, time zone name changes.
The tz database version used by QuestDB is determined by the JDK version used at build time and therefore updates to the time zone database are directly influenced by this JDK version. To find the JDK version used by a QuestDB build, run the following SQL:
SELECT build();
| build |
|---|
| Build Information: QuestDB 7.4.0, JDK 11.0.8, Commit Hash b9776a8a09f7db35955530bff64de488a029f1ce |
Converting timestamps to and from time zones​
For convenience, QuestDB includes two functions for time zone conversions on timestamp values.
These functions are used to convert a Unix timestamp, or a string equivalent cast to timestamp as follows:
SELECT to_timezone(1623167145000000, 'Europe/Berlin');
| to_timezone |
|---|
| 2021-06-08T17:45:45.000000Z |
SELECT to_utc(1623167145000000, 'Europe/Berlin');
| to_utc |
|---|
| 2021-06-08T13:45:45.000000Z |
Using UTC offset for conversions​
The to_timezone() and
to_utc() functions may use UTC
offset for converting timestamp values. In some cases, this can be more reliable
than string or time zone ID conversion given historic changes to time zone names
or transitions. The following example takes a Unix timestamp in microseconds and
converts it to a time zone +2 hours offset from UTC:
SELECT to_timezone(1213086329000000, '+02:00');
| to_timezone |
|---|
| 2008-06-10T10:25:29.000000Z |
SELECT to_utc('2008-06-10T10:25:29.000000Z', '+02:00');
| to_timezone |
|---|
| 2008-06-10T08:25:29.000000Z |