Search Shortcut cmd + k | ctrl + k
Timestamp with Time Zone Functions

This section describes functions and operators for examining and manipulating TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) values.

Despite the name, these values do not store a time zone - just an instant like TIMESTAMP. Instead, they request that the instant be binned and formatted using the current time zone.

Time zone support is not built in but can be provided by an extension, such as the ICU extension that ships with DuckDB.

In the examples below, the current time zone is presumed to be America/Los_Angeles using the Gregorian calendar.

Built-In Timestamp with Time Zone Functions

The table below shows the available scalar functions for TIMESTAMPTZ values. Since these functions do not involve binning or display, they are always available.

Function Description Example Result
current_timestamp Current date and time (start of current transaction) current_timestamp 2022-10-08 12:44:46.122-07
get_current_timestamp() Current date and time (start of current transaction) get_current_timestamp() 2022-10-08 12:44:46.122-07
greatest(timestamptz, timestamptz) The later of two timestamps greatest(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234') 1992-09-20 20:38:48-07
isfinite(timestamptz) Returns true if the timestamp with time zone is finite, false otherwise isfinite(TIMESTAMPTZ '1992-03-07') true
isinf(timestamptz) Returns true if the timestamp with time zone is infinite, false otherwise isinf(TIMESTAMPTZ '-infinity') true
least(timestamptz, timestamptz) The earlier of two timestamps least(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234') 1992-03-22 01:02:03.1234-08
now() Current date and time (start of current transaction) now() 2022-10-08 12:44:46.122-07
transaction_timestamp() Current date and time (start of current transaction) transaction_timestamp() 2022-10-08 12:44:46.122-07

Timestamp with Time Zone Strings

With no time zone extension loaded, TIMESTAMPTZ values will be cast to and from strings using offset notation. This will let you specify an instant correctly without access to time zone information. For portability, TIMESTAMPTZ values will always be displayed using GMT offsets:

SELECT '2022-10-08 13:13:34-07'::TIMESTAMPTZ;
-- 2022-10-08 20:13:34+00

If a time zone extension such as ICU is loaded, then a time zone can be parsed from a string and cast to a representation in the local time zone:

SELECT '2022-10-08 13:13:34 Europe/Amsterdam'::TIMESTAMPTZ::VARCHAR;
-- 2022-10-08 04:13:34-07 -- the offset will differ based on your local time zone

ICU Timestamp with Time Zone Operators

The table below shows the available mathematical operators for TIMESTAMP WITH TIME ZONE values provided by the ICU extension.

Operator Description Example Result
+ addition of an INTERVAL TIMESTAMPTZ '1992-03-22 01:02:03' + INTERVAL 5 DAY 1992-03-27 01:02:03
- subtraction of TIMESTAMPTZs TIMESTAMPTZ '1992-03-27' - TIMESTAMPTZ '1992-03-22' 5 days
- subtraction of an INTERVAL TIMESTAMPTZ '1992-03-27 01:02:03' - INTERVAL 5 DAY 1992-03-22 01:02:03

Adding to or subtracting from infinite values produces the same infinite value.

ICU Timestamp with Time Zone Functions

The table below shows the ICU provided scalar functions for TIMESTAMP WITH TIME ZONE values.

Function Description Example Result
age(timestamptz, timestamptz) Subtract arguments, resulting in the time difference between the two timestamps age(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '1992-09-20') 8 years 6 months 20 days
age(timestamptz) Subtract from current_date age(TIMESTAMP '1992-09-20') 29 years 1 month 27 days 12:39:00.844
date_diff(part, startdate, enddate) The number of partition boundaries between the timestamps date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00') 2
date_part([part, ...], timestamptz) Get the listed subfields as a struct. The list must be constant. date_part(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07') {year: 1992, month: 9, day: 20}
date_part(part, timestamptz) Get subfield (equivalent to extract) date_part('minute', TIMESTAMPTZ '1992-09-20 20:38:40') 38
date_sub(part, startdate, enddate) The number of complete partitions between the timestamps date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00') 1
date_trunc(part, timestamptz) Truncate to specified precision date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40') 1992-09-20 20:00:00
datediff(part, startdate, enddate) Alias of date_diff. The number of partition boundaries between the timestamps datediff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00') 2
datepart([part, ...], timestamptz) Alias of date_part. Get the listed subfields as a struct. The list must be constant. datepart(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07') {year: 1992, month: 9, day: 20}
datepart(part, timestamptz) Alias of date_part. Get subfield (equivalent to extract) datepart('minute', TIMESTAMPTZ '1992-09-20 20:38:40') 38
datesub(part, startdate, enddate) Alias of date_sub. The number of complete partitions between the timestamps datesub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00') 1
datetrunc(part, timestamptz) Alias of date_trunc. Truncate to specified precision datetrunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40') 1992-09-20 20:00:00
epoch_ms(timestamptz) Converts a timestamptz to milliseconds since the epoch epoch_ms('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ); 1667810584123
epoch_ns(timestamptz) Converts a timestamptz to nanoseconds since the epoch epoch_ns('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ); 1667810584123456000
epoch_us(timestamptz) Converts a timestamptz to microseconds since the epoch epoch_us('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ); 1667810584123456
extract(field from timestamptz) Get subfield from a timestamp with time zone extract('hour' FROM TIMESTAMPTZ '1992-09-20 20:38:48') 20
last_day(timestamptz) The last day of the month. last_day(TIMESTAMPTZ '1992-03-22 01:02:03.1234') 1992-03-31
make_timestamptz(bigint, bigint, bigint, bigint, bigint, double, string) The timestamp with time zone for the given parts and time zone make_timestamptz(1992, 9, 20, 15, 34, 27.123456, 'CET') 1992-09-20 06:34:27.123456-07
make_timestamptz(bigint, bigint, bigint, bigint, bigint, double) The timestamp with time zone for the given parts in the current time zone make_timestamptz(1992, 9, 20, 13, 34, 27.123456) 1992-09-20 13:34:27.123456-07
make_timestamptz(microseconds) The timestamp with time zone for the given µs since the epoch make_timestamptz(1667810584123456) 2022-11-07 16:43:04.123456-08
strftime(timestamptz, format) Converts timestamp with time zone to string according to the format string strftime(timestamptz '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p') Wed, 1 January 1992 - 08:38:40 PM
strptime(text, format) Converts string to timestamp with time zone according to the format string if %Z is specified. strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z') 1992-01-01 08:38:40-08
time_bucket(bucket_width, timestamptz[, offset]) Truncate timestamptz by the specified interval bucket_width. Buckets are offset by offset interval. time_bucket(INTERVAL '10 minutes', TIMESTAMPTZ '1992-04-20 15:26:00-07', INTERVAL '5 minutes') 1992-04-20 15:25:00-07
time_bucket(bucket_width, timestamptz[, origin]) Truncate timestamptz by the specified interval bucket_width. Buckets are aligned relative to origin timestamptz. origin defaults to 2000-01-03 00:00:00+00 for buckets that don’t include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets. time_bucket(INTERVAL '2 weeks', TIMESTAMPTZ '1992-04-20 15:26:00-07', TIMESTAMPTZ '1992-04-01 00:00:00-07') 1992-04-15 00:00:00-07
time_bucket(bucket_width, timestamptz[, timezone]) Truncate timestamptz by the specified interval bucket_width. Bucket starts and ends are calculated using timezone. timezone is a varchar and defaults to UTC. time_bucket(INTERVAL '2 days', TIMESTAMPTZ '1992-04-20 15:26:00-07', 'Europe/Berlin') 1992-04-19 15:00:00-07

There are also dedicated extraction functions to get the subfields.

ICU Timestamp Table Functions

The table below shows the available table functions for TIMESTAMP WITH TIME ZONE types.

Function Description Example
generate_series(timestamptz, timestamptz, interval) Generate a table of timestamps in the closed range (including both the starting timestamp and the ending timestamp), stepping by the interval generate_series(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)
range(timestamptz, timestamptz, interval) Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp) , stepping by the interval range(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)

Infinite values are not allowed as table function bounds.

ICU Timestamp Without Time Zone Functions

The table below shows the ICU provided scalar functions that operate on plain TIMESTAMP values. These functions assume that the TIMESTAMP is a “local timestamp”.

A local timestamp is effectively a way of encoding the part values from a time zone into a single value. They should be used with caution because the produced values can contain gaps and ambiguities thanks to daylight savings time. Often the same functionality can be implemented more reliably using the struct variant of the date_part function.

Function Description Example Result
current_localtime() Returns a TIME whose GMT bin values correspond to local time in the current time zone. current_localtime() 08:47:56.497
current_localtimestamp() Returns a TIMESTAMP whose GMT bin values correspond to local date and time in the current time zone. current_localtimestamp() 2022-12-17 08:47:56.497
localtime Synonym for the current_localtime() function call. localtime 08:47:56.497
localtimestamp Synonym for the current_localtimestamp() function call. localtimestamp 2022-12-17 08:47:56.497
timezone(text, timestamp) Use the date parts of the timestamp in GMT to construct a timestamp in the given time zone. Effectively, the argument is a “local” time. timezone('America/Denver', TIMESTAMP '2001-02-16 20:38:40') 2001-02-16 19:38:40-08
timezone(text, timestamptz) Use the date parts of the timestamp in the given time zone to construct a timestamp. Effectively, the result is a “local” time. timezone('America/Denver', TIMESTAMPTZ '2001-02-16 20:38:40-05') 2001-02-16 18:38:40

At Time Zone

The AT TIME ZONE syntax is syntactic sugar for the (two argument) timezone function listed above:

timestamp '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
-- 2001-02-16 19:38:40-08
timestamp with time zone '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
-- 2001-02-16 18:38:40

Infinities

Functions applied to infinite dates will either return the same infinite dates (e.g, greatest) or NULL (e.g., date_part) depending on what “makes sense”. In general, if the function needs to examine the parts of the infinite temporal value, the result will be NULL.

Calendars

The ICU extension also supports non-Gregorian calendars. If such a calendar is current, then the display and binning operations will use that calendar.

About this page

Last modified: 2024-03-02