Copy Markdown
Timestamp with Time Zone Functions
This section describes functions and operators for examining and manipulating TIMESTAMP WITH TIME ZONE
(or TIMESTAMPTZ) valuesTIMESTAMP functions
Time zone support is provided by the built-in ICU extension .
In the examples below, the current time zone is presumed to be America/Los_Angeles
using the Gregorian calendar.
The table below shows the available scalar functions for TIMESTAMPTZ values.
Since these functions do not involve binning or display,
they are always available.
Description Current date and time (start of current transaction).
Example current_timestamp
Result 2022-10-08 12:44:46.122-07
Description Current date and time (start of current transaction).
Example get_current_timestamp()
Result 2022-10-08 12:44:46.122-07
Description The later of two timestamps.
Example greatest(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234')
Result 1992-09-20 20:38:48-07
Description Returns true if the timestamp with time zone is finite, false otherwise.
Example isfinite(TIMESTAMPTZ '1992-03-07')
Result true
Description Returns true if the timestamp with time zone is infinite, false otherwise.
Example isinf(TIMESTAMPTZ '-infinity')
Result true
Description The earlier of two timestamps.
Example least(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234')
Result 1992-03-22 01:02:03.1234-08
Description Current date and time (start of current transaction).
Example now()
Result 2022-10-08 12:44:46.122-07
Description Converts seconds since the epoch to a timestamp with time zone.
Example to_timestamp(1284352323.5)
Result 2010-09-13 04:32:03.5+00
Description Current date and time (start of current transaction).
Example transaction_timestamp()
Result 2022-10-08 12:44:46.122-07
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 ;
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
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 DAY1992-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 DAY1992-03-22 01:02:03
Adding to or subtracting from infinite values produces the same infinite value.
The table below shows the ICU provided scalar functions for TIMESTAMP WITH TIME ZONE values.
Name
Description
age(timestamptz, timestamptz)Subtract arguments, resulting in the time difference between the two timestamps.
age(timestamptz)Subtract from current_date.
date_diff(part, startdate, enddate)The number of partition boundaries between the timestamps.
date_part([part, ...], timestamptz)Get the listed subfields as a struct. The list must be constant.
date_part(part, timestamptz)Get subfield (equivalent to extract ).
date_sub(part, startdate, enddate)The number of complete partitions between the timestamps.
date_trunc(part, timestamptz)Truncate to specified precision .
datediff(part, startdate, enddate)Alias of date_diff. The number of partition boundaries between the timestamps.
datepart([part, ...], timestamptz)Alias of date_part. Get the listed subfields as a struct. The list must be constant.
datepart(part, timestamptz)Alias of date_part. Get subfield (equivalent to extract ).
datesub(part, startdate, enddate)Alias of date_sub. The number of complete partitions between the timestamps.
datetrunc(part, timestamptz)Alias of date_trunc. Truncate to specified precision .
epoch_ms(timestamptz)Converts a timestamptz to milliseconds since the epoch.
epoch_ns(timestamptz)Converts a timestamptz to nanoseconds since the epoch.
epoch_us(timestamptz)Converts a timestamptz to microseconds since the epoch.
extract(field FROM timestamptz)Get subfield from a TIMESTAMP WITH TIME ZONE.
last_day(timestamptz)The last day of the month.
make_timestamptz(bigint, bigint, bigint, bigint, bigint, double, string)The TIMESTAMP WITH TIME ZONE for the given parts and time zone.
make_timestamptz(bigint, bigint, bigint, bigint, bigint, double)The TIMESTAMP WITH TIME ZONE for the given parts in the current time zone.
make_timestamptz(microseconds)The TIMESTAMP WITH TIME ZONE for the given µs since the epoch.
strftime(timestamptz, format)Converts a TIMESTAMP WITH TIME ZONE value to string according to the format string .
strptime(text, format)Converts string to TIMESTAMP WITH TIME ZONE according to the format string if %Z is specified.
time_bucket(bucket_width, timestamptz[, offset])Truncate timestamptz by the specified interval bucket_width. Buckets are offset by offset interval.
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(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.
Description Subtract arguments, resulting in the time difference between the two timestamps.
Example age(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '1992-09-20')
Result 8 years 6 months 20 days
Description Subtract from current_date.
Example age(TIMESTAMP '1992-09-20')
Result 29 years 1 month 27 days 12:39:00.844
Description The number of partition boundaries between the timestamps.
Example date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result 2
Description Get the listed subfields as a struct. The list must be constant.
Example date_part(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07')
Result {year: 1992, month: 9, day: 20}
Description Get subfield (equivalent to extract ).
Example date_part('minute', TIMESTAMPTZ '1992-09-20 20:38:40')
Result 38
Description The number of complete partitions between the timestamps.
Example date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result 1
Description Truncate to specified precision .
Example date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
Result 1992-09-20 20:00:00
Description Alias of date_diff. The number of partition boundaries between the timestamps.
Example datediff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result 2
Description Alias of date_part. Get the listed subfields as a struct. The list must be constant.
Example datepart(['year', 'month', 'day'], TIMESTAMPTZ '1992-09-20 20:38:40-07')
Result {year: 1992, month: 9, day: 20}
Description Alias of date_part. Get subfield (equivalent to extract ).
Example datepart('minute', TIMESTAMPTZ '1992-09-20 20:38:40')
Result 38
Description Alias of date_sub. The number of complete partitions between the timestamps.
Example datesub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result 1
Description Alias of date_trunc. Truncate to specified precision .
Example datetrunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
Result 1992-09-20 20:00:00
Description Converts a timestamptz to milliseconds since the epoch.
Example epoch_ms('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);
Result 1667810584123
Description Converts a timestamptz to nanoseconds since the epoch.
Example epoch_ns('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);
Result 1667810584123456000
Description Converts a timestamptz to microseconds since the epoch.
Example epoch_us('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ);
Result 1667810584123456
Description Get subfield from a TIMESTAMP WITH TIME ZONE.
Example extract('hour' FROM TIMESTAMPTZ '1992-09-20 20:38:48')
Result 20
Description The last day of the month.
Example last_day(TIMESTAMPTZ '1992-03-22 01:02:03.1234')
Result 1992-03-31
Description The TIMESTAMP WITH TIME ZONE for the given parts and time zone.
Example make_timestamptz(1992, 9, 20, 15, 34, 27.123456, 'CET')
Result 1992-09-20 06:34:27.123456-07
Description The TIMESTAMP WITH TIME ZONE for the given parts in the current time zone.
Example make_timestamptz(1992, 9, 20, 13, 34, 27.123456)
Result 1992-09-20 13:34:27.123456-07
Description The TIMESTAMP WITH TIME ZONE for the given µs since the epoch.
Example make_timestamptz(1667810584123456)
Result 2022-11-07 16:43:04.123456-08
Description Converts a TIMESTAMP WITH TIME ZONE value to string according to the format string .
Example strftime(timestamptz '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p')
Result Wed, 1 January 1992 - 08:38:40 PM
Description Converts string to TIMESTAMP WITH TIME ZONE according to the format string if %Z is specified.
Example strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z')
Result 1992-01-01 08:38:40-08
Description Truncate timestamptz by the specified interval bucket_width. Buckets are offset by offset interval.
Example time_bucket(INTERVAL '10 minutes', TIMESTAMPTZ '1992-04-20 15:26:00-07', INTERVAL '5 minutes')
Result 1992-04-20 15:25:00-07
Description 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.
Example time_bucket(INTERVAL '2 weeks', TIMESTAMPTZ '1992-04-20 15:26:00-07', TIMESTAMPTZ '1992-04-01 00:00:00-07')
Result 1992-04-15 00:00:00-07
Description Truncate timestamptz by the specified interval bucket_width. Bucket starts and ends are calculated using timezone. timezone is a varchar and defaults to UTC.
Example time_bucket(INTERVAL '2 days', TIMESTAMPTZ '1992-04-20 15:26:00-07', 'Europe/Berlin')
Result 1992-04-19 15:00:00-07
There are also dedicated extraction functions to get the subfields .
The table below shows the available table functions for TIMESTAMP WITH TIME ZONE types.
Infinite values are not allowed as table function bounds.
Description Generate a table of timestamps in the closed range (including both the starting timestamp and the ending timestamp), stepping by the interval.
Example generate_series(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)
Description Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp), stepping by the interval.
Example range(TIMESTAMPTZ '2001-04-10', TIMESTAMPTZ '2001-04-11', INTERVAL 30 MINUTE)
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.
Name
Description
current_localtime()Returns a TIME whose GMT bin values correspond to local time in the current time zone.
current_localtimestamp()Returns a TIMESTAMP whose GMT bin values correspond to local date and time in the current time zone.
localtimeSynonym for the current_localtime() function call.
localtimestampSynonym for the current_localtimestamp() function call.
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(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.
Description Returns a TIME whose GMT bin values correspond to local time in the current time zone.
Example current_localtime()
Result 08:47:56.497
Description Returns a TIMESTAMP whose GMT bin values correspond to local date and time in the current time zone.
Example current_localtimestamp()
Result 2022-12-17 08:47:56.497
Description Synonym for the current_localtime() function call.
Example localtime
Result 08:47:56.497
Description Synonym for the current_localtimestamp() function call.
Example localtimestamp
Result 2022-12-17 08:47:56.497
Description 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.
Example timezone('America/Denver', TIMESTAMP '2001-02-16 20:38:40')
Result 2001-02-16 19:38:40-08
Description Use the date parts of the timestamp in the given time zone to construct a timestamp. Effectively, the result is a “local” time.
Example timezone('America/Denver', TIMESTAMPTZ '2001-02-16 20:38:40-05')
Result 2001-02-16 18:38:40
The AT TIME ZONE syntax is syntactic sugar for the (two argument) timezone function listed above:
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver' AS ts ;
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver' AS ts ;
Note that numeric timezones are not allowed:
SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE '0200' AS ts ;
Not implemented Error: Unknown TimeZone '0200'
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.
The ICU extension also supports non-Gregorian calendars .
If such a calendar is current, then the display and binning operations will use that calendar.
© 2026 DuckDB Foundation, Amsterdam NL