The table below shows the available scalar functions for TIMESTAMPTZ values.
Since these functions do not involve binning or display,
they are always available.
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:
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.
Addition and subtraction of intervals uses the ICU Calendar add function.
For positive intervals (forwards in time) the fields are incremented from least to most significant.
For negative intervals (backwards in time) the fields are decremented from most to least significant.
This produces the same results as Postgres, but does not match some more recent calendar RFCs.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at 2000-01-01 00:00:00+00:00[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00+00:00[ + offset]. Note that 2000-01-03 is a Monday.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 00:00:00+00:00 when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00+00:00. Note that 2000-01-03 is a Monday.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 00:00:00 in the provided timezone when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00 in the provided timezone. The default timezone is 'UTC'. Note that 2000-01-03 is a Monday.
The signed number of part boundaries between starttimestamptz and endtimestamptz, inclusive of the larger timestamp and exclusive of the smaller timestamp.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at 2000-01-01 00:00:00+00:00[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00+00:00[ + offset]. Note that 2000-01-03 is a Monday.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 00:00:00+00:00 when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00+00:00. Note that 2000-01-03 is a Monday.
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 00:00:00 in the provided timezone when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00 in the provided timezone. The default timezone is 'UTC'. Note that 2000-01-03 is a Monday.
Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp), stepping by the interval.
Infinite values are not allowed as table function bounds.
Generate a table of timestamps in the half open range (including the starting timestamp, but stopping before the ending timestamp), stepping by the interval.
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.
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.
When adding calendar intervals such as INTERVAL '1 day' to a
TIMESTAMPTZ, the resulting local timestamp may fall on a
non-existent time during daylight saving time transitions.
DuckDB follows PostgreSQL behavior and adjusts the result forward
to the next valid timestamp.
