Timestamp Types

Timestamps represent points in time. As such, they combine DATE and TIME information. They can be created using the type name followed by a string formatted according to the ISO 8601 format, YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]], which is also the format we use in this documentation. Decimal places beyond the supported precision are ignored.

Timestamp Types

Name	Aliases	Description
TIMESTAMP_NS		Naive timestamp with nanosecond precision
TIMESTAMP	DATETIME, TIMESTAMP WITHOUT TIME ZONE	Naive timestamp with microsecond precision
TIMESTAMP_MS		Naive timestamp with millisecond precision
TIMESTAMP_S		Naive timestamp with second precision
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	Time zone aware timestamp with microsecond precision

Attention

Since there is not currently a TIMESTAMP_NS WITH TIME ZONE data type, external columns with nanosecond precision and WITH TIME ZONE semantics, e.g., Parquet timestamp columns with isAdjustedToUTC=true, are converted to TIMESTAMP WITH TIME ZONE and thus lose precision when read using SereneDB.

Query

SELECT TIMESTAMP_NS '1992-09-20 11:30:00.123456789';

Result

 timestamp_ns------------------------------- 1992-09-20 11:30:00.123456789

Query

SELECT TIMESTAMP '1992-09-20 11:30:00.123456789';

Result

 timestamp---------------------------- 1992-09-20 11:30:00.123456

Query

SELECT TIMESTAMP_MS '1992-09-20 11:30:00.123456789';
SELECT TIMESTAMP_MS '1992-09-20 11:30:00.123456789';

Result

 timestamp_ms------------------------- 1992-09-20 11:30:00.123
 timestamp_ms------------------------- 1992-09-20 11:30:00.123

Query

SELECT TIMESTAMP_S '1992-09-20 11:30:00.123456789';

Result

 timestamp_s--------------------- 1992-09-20 11:30:00

Query

SELECT TIMESTAMPTZ '1992-09-20 11:30:00.123456789';

Result

 timestamp with time zone------------------------------- 1992-09-20 11:30:00.123456+00

Query

SELECT TIMESTAMPTZ '1992-09-20 12:30:00.123456789+01:00';

Result

 timestamp with time zone------------------------------- 1992-09-20 11:30:00.123456+00

SereneDB distinguishes timestamps WITHOUT TIME ZONE and WITH TIME ZONE (of which the only current representative is TIMESTAMP WITH TIME ZONE).

Despite the name, a TIMESTAMP WITH TIME ZONE does not store time zone information. Instead, it only stores the INT64 number of non-leap microseconds since the Unix epoch 1970-01-01 00:00:00+00, and thus unambiguously identifies a point in absolute time, or instant. The reason for the labels time zone aware and WITH TIME ZONE is that timestamp arithmetic, binning, and string formatting for this type are performed in a configured time zone, which defaults to the system time zone and is just UTC+00:00 in the examples above.

The corresponding TIMESTAMP WITHOUT TIME ZONE stores the same INT64, but arithmetic, binning and string formatting follow the straightforward rules of Coordinated Universal Time (UTC) without offsets or time zones. Accordingly, TIMESTAMPs could be interpreted as UTC timestamps, but more commonly they are used to represent local observations of time recorded in an unspecified time zone, and operations on these types can be interpreted as simply manipulating tuple fields following nominal temporal logic. It is a common data cleaning problem to disambiguate such observations, which may also be stored in raw strings without time zone specification or UTC offsets, into unambiguous TIMESTAMP WITH TIME ZONE instants. One possible solution to this is to append UTC offsets to strings, followed by an explicit cast to TIMESTAMP WITH TIME ZONE. Alternatively, a TIMESTAMP WITHOUT TIME ZONE may be created first and then be combined with a time zone specification to obtain a time zone aware TIMESTAMP WITH TIME ZONE.

Conversion between Strings and Naïve / Time Zone-Aware Timestamps

The conversion between strings without UTC offsets or IANA time zone names and WITHOUT TIME ZONE types is unambiguous and straightforward. The conversion between strings with UTC offsets or time zone names and WITH TIME ZONE types is also unambiguous.

When strings without UTC offsets or time zone names are converted to a WITH TIME ZONE type, the string is interpreted in the configured time zone. When strings with UTC offsets are passed to a WITHOUT TIME ZONE type, the offsets or time zone specifications are ignored. When strings with time zone names other than UTC are passed to a WITHOUT TIME ZONE type, an error is thrown.

Finally, when WITH TIME ZONE and WITHOUT TIME ZONE types are converted to each other via explicit or implicit casts, the translation uses the configured time zone.

To use an alternative time zone, the timezone function may be used:

Query

SELECT    timezone('America/Denver', TIMESTAMP '2001-02-16 20:38:40') AS aware1,    timezone('America/Denver', TIMESTAMPTZ '2001-02-16 04:38:40') AS naive1,    timezone('UTC', TIMESTAMP '2001-02-16 20:38:40+00:00') AS aware2,    timezone('UTC', TIMESTAMPTZ '2001-02-16 04:38:40 Europe/Berlin') AS naive2;

Result

 aware1                 | naive1              | aware2                 | naive2------------------------+---------------------+------------------------+--------------------- 2001-02-17 03:38:40+00 | 2001-02-15 21:38:40 | 2001-02-16 20:38:40+00 | 2001-02-16 03:38:40

Note that TIMESTAMPs are displayed without time zone specification in the results, following ISO 8601 rules for local times, while time-zone aware TIMESTAMPTZs are displayed with the UTC offset of the configured time zone, which is 'Europe/Berlin' in the example. The UTC offsets of 'America/Denver' and 'Europe/Berlin' at all involved instants are -07:00 and +01:00, respectively.

Special Values

Three special strings can be used to create timestamps:

Input string	Description
epoch	1970-01-01 00:00:00[+00] (Unix system time zero)
infinity	Later than all other timestamps
-infinity	Earlier than all other timestamps

The values infinity and -infinity are special cased and are displayed unchanged, whereas the value epoch is simply a notational shorthand that is converted to the corresponding timestamp value when read.

Query

SELECT '-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, 'infinity'::TIMESTAMP;

Result

 timestamp | timestamp           | timestamp-----------+---------------------+----------- -infinity | 1970-01-01 00:00:00 | infinity

Functions

See Timestamp Functions.

Time Zones

To understand time zones and the WITH TIME ZONE types, it helps to start with two concepts: instants and temporal binning.

Instants

An instant is a point in absolute time, usually given as a count of some time increment from a fixed point in time (called the epoch). This is similar to how positions on the earth's surface are given using latitude and longitude relative to the equator and the Greenwich Meridian. In SereneDB, the fixed point is the Unix epoch 1970-01-01 00:00:00+00:00, and the increment is in seconds, milliseconds, microseconds, or nanoseconds, depending on the specific data type.

Temporal Binning

Binning is a common practice with continuous data: A range of possible values is broken up into contiguous subsets and the binning operation maps actual values to the bin they fall into. Temporal binning is simply applying this practice to instants; for example, by binning instants into years, months and days.

Temporal binning rules are complex, and generally come in two sets: time zones and calendars. For most tasks, the calendar will just be the widely used Gregorian calendar, but time zones apply locale-specific rules and can vary widely. For example, here is what binning for the 'America/Los_Angeles' time zone looks like near the epoch:

The most common temporal binning problem occurs when daylight saving time changes. The example below contains a daylight saving time change where the "hour" bin is two hours long. To distinguish the two hours, another range of bins containing the offset from UTC is needed:

Time Zone Support

The TIMESTAMPTZ type can be binned into calendar and clock bins. SereneDB implements the binning and arithmetic functions using the International Components for Unicode time zone and calendar functions.

To set the time zone, use the SET TimeZone command:

Query

SET TimeZone = 'America/Los_Angeles';

Time binning operations for TIMESTAMPTZ will then be implemented using the given time zone.

{/* A list of available time zones can be pulled from the pg_timezone_names() table function, which is not yet available. See https://github.com/serenedb/serenedb/issues/820

Query

SELECT    name,    abbrev,    utc_offsetFROM pg_timezone_names()ORDER BY    name;

Result

 name                | abbrev | utc_offset---------------------+--------+------------ America/Los_Angeles | PST    | -08:00:00 Europe/Berlin       | CET    | 01:00:00 UTC                 | UTC    | 00:00:00

You can find a reference table of available time zones. */}

Calendar Support

SereneDB also supports non-Gregorian calendars using the SET Calendar command.

Query

SET Calendar = 'japanese';

Time binning operations for TIMESTAMPTZ will then be implemented using the given calendar. In this example, the era part will now report the Japanese imperial era number.

A list of available calendars can be pulled from the icu_calendar_names() table function:

Query

SELECT nameFROM icu_calendar_names()ORDER BY 1;

Result

 name--------------------- buddhist chinese coptic dangi ethiopic ethiopic-amete-alem gregorian hebrew indian islamic islamic-civil islamic-rgsa islamic-tbla islamic-umalqura iso8601 japanese persian roc

Settings

The current value of the TimeZone and Calendar settings are determined by ICU when it starts up. They can be queried from the duckdb_settings() table function:

Query

SELECT *FROM duckdb_settings()WHERE name = 'TimeZone';

Query

SELECT *FROM duckdb_settings()WHERE name = 'Calendar';

name	value	description	input_type
Calendar	gregorian	The current calendar	VARCHAR

Tip: If you find that your binning operations are not behaving as you expect, check the TimeZone and Calendar values and adjust them if needed.