Timestamp Type
Version 0.3.2

Timestamps represent points in absolute time, usually called instants. DuckDB represents instants as the number of microseconds (┬Ás) since 1970-01-01 00:00:00+00.

Name Aliases Description
TIMESTAMP datetime time of day (ignores time zone)
TIMESTAMP WITH TIME ZONE TIMESTAMPTZ time of day (uses time zone)

A timestamp specifies a combination of DATE (year, month, day) and a TIME (hour, minute, second, millisecond). Timestamps can be created using the TIMESTAMP keyword, where the data must be formatted according to the ISO 8601 format (YYYY-MM-DD hh:mm:ss[.zzzzzz][+-TT[:tt]]).

-- 11:30 AM at 20 September, 1992 GMT
SELECT TIMESTAMP '1992-09-20 11:30:00';
-- 2:30 PM at 20 September, 1992 GMT
SELECT TIMESTAMP '1992-09-20 14:30:00';

Functions

See Timestamp Functions.

Time Zones

The TIMESTAMPTZ type can be binned into calendar and clock bins using a suitable extension. The built in ICU extension implements all the binning and arithmetic functions using the International Components for Unicode time zone and calendar functions.

To set the time zone to use, first load the ICU extension. The ICU extension comes pre-bundled with several DuckDB clients (including Python, R, JDBC, and ODBC), so this step can be skipped in those cases.

require icu

Next, use the Set TimeZone command:

Set TimeZone='Americas/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:

SELECT 
    name,
    abbrev,
    utc_offset 
FROM pg_timezone_names() 
ORDER BY 
    name;

You can also find a reference table of available time zones here.

Calendars

The ICU extension also supports non-Gregorian calendars using the Set Calendar command. Note that the require icu step is only required if the DuckDB client does not bundle the ICU extension.

require icu

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:

SELECT name FROM icu_calendar_names() ORDER BY 1;

Settings

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

SELECT * FROM duckdb_settings() WHERE name = 'TimeZone';
-- Americas/Los_Angeles
SELECT * FROM duckdb_settings() WHERE name = 'Calendar';
-- gregorian