When working with timestamped data, it may be necessary to convert timestampvalues to or from UTC, or to offset timestamp values by a fixed duration. Thefollowing sections describe how QuestDB handles timestamps natively, how to usebuilt-in functions for working with time zone conversions, and general hints forworking with time zones in QuestDB.

Timestamps in QuestDB​

The native timestamp format used by QuestDB is a Unix timestamp in microsecondresolution. Although timestamps in nanoseconds will be parsed, the output willbe truncated to microseconds. QuestDB does not store time zone informationalongside timestamp values and therefore it should be assumed that alltimestamps are in UTC.

The following example shows how a Unix timestamp in microseconds may be passedinto a timestamp column directly:

CREATE TABLE my_table (ts timestamp, col1 int) timestamp(ts);
INSERT INTO my_table VALUES(1623167145123456, 12);
my_table;

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;

When inserting timestamps into a table, it is also possible to usetimestamp unitsto define the timestamp format, in order to process trailing zeros in exporteddata 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:

QuestDB's internal time zone database​

In order to simplify working with time zones, QuestDB usesthe tz time zone database which isstandard in the Java ecosystem. This time zone database is used internally intime zone lookup and in operations relating to timestamp value conversion to andfrom time zones.

For this reason, a time zone may be referenced by abbreviated name, by full timezone name or by UTC offset:

Referring to time zones​

It's strongly advisednot to use the three-letter ID or abbreviation fortime 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"), andthe 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") ismore reliable when referring to time zones. Instructions for converting to andfrom time zones are described in theConverting timestamps to and from time zonessection below.

The current QuestDB time zone database uses theEnglish locale but supportfor additional locales may be added in future. Referring to time zones which areoutdated or not recognized results in ainvalid timezone name error. Thefollowing resources may be used for hints how to refer to time zones by ID oroffset:

• Theofficial list maintained by IANA
• Java'sgetAvailableZoneIdsmethod
• Wiki entry on tz database time zones(this is a convenient reference, but may not be 100% accurate)

Updates to the time zone database​

The upstream project updates past time zones as new information becomesavailable. 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 atbuild time and therefore updates to the time zone database are directlyinfluenced by this JDK version. To find the JDK version used by a QuestDB build,run the following SQL:

SELECT build();

Converting timestamps to and from time zones​

For convenience, QuestDB includes two functions for time zone conversions ontimestamp values.

• to_timezone()
• to_utc()

These functions are used to convert a Unix timestamp, or a string equivalentcast to timestamp as follows:

SELECT to_timezone(1623167145000000, 'Europe/Berlin');

SELECT to_utc(1623167145000000, 'Europe/Berlin');

Using UTC offset for conversions​

Theto_timezone() andto_utc() functions may use UTCoffset for converting timestamp values. In some cases, this can be more reliablethan string or time zone ID conversion given historic changes to time zone namesor transitions. The following example takes a Unix timestamp in microseconds andconverts it to a time zone+2 hours offset from UTC:

SELECT to_timezone(1213086329000000, '+02:00');

SELECT to_utc('2008-06-10T10:25:29.000000Z', '+02:00');