Timestamp with Time Zone Functions

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

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.

Name	Description
current_timestamp	Current date and time (start of current transaction).
get_current_timestamp()	Current date and time (start of current transaction).
greatest(timestamptz, timestamptz)	The later of two timestamps.
isfinite(timestamptz)	Returns true if the timestamp with time zone is finite, false otherwise.
isinf(timestamptz)	Returns true if the timestamp with time zone is infinite, false otherwise.
least(timestamptz, timestamptz)	The earlier of two timestamps.
now()	Current date and time (start of current transaction).
timetz_byte_comparable(timetz)	Converts a TIME WITH TIME ZONE to a UBIGINT sort key.
to_timestamp(double)	Converts seconds since the epoch to a timestamp with time zone.
transaction_timestamp()	Current date and time (start of current transaction).

current_timestamp

Description	Current date and time (start of current transaction).
Example	current_timestamp
Result	2022-10-08 12:44:46.122-07

get_current_timestamp()

Description	Current date and time (start of current transaction).
Example	get_current_timestamp()
Result	2022-10-08 12:44:46.122-07

greatest(timestamptz, timestamptz)

The later of two timestamps.

SQL

SELECT greatest(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234') AS greatest;

 greatest
------------------------
 1992-09-20 20:38:48+00

isfinite(timestamptz)

Returns true if the timestamp with time zone is finite, false otherwise.

SQL

SELECT isfinite(TIMESTAMPTZ '1992-03-07') AS isfinite;

 isfinite
----------
 t

isinf(timestamptz)

Returns true if the timestamp with time zone is infinite, false otherwise.

SQL

SELECT isinf(TIMESTAMPTZ '-infinity') AS isinf;

 isinf
-------
 t

least(timestamptz, timestamptz)

The earlier of two timestamps.

SQL

SELECT least(TIMESTAMPTZ '1992-09-20 20:38:48', TIMESTAMPTZ '1992-03-22 01:02:03.1234') AS least;

 least
-----------------------------
 1992-03-22 01:02:03.1234+00

now()

Description	Current date and time (start of current transaction).
Example	now()
Result	2022-10-08 12:44:46.122-07

timetz_byte_comparable(timetz)

Converts a TIME WITH TIME ZONE to a UBIGINT sort key.

SQL

SELECT timetz_byte_comparable('18:18:16.21-07:00'::TIMETZ) AS sort_key;

 sort_key
---------------------
 2494691656335442799

to_timestamp(double)

Converts seconds since the epoch to a timestamp with time zone.

SQL

SELECT to_timestamp(1284352323.5) AS ts;

 ts
--------------------------
 2010-09-13 04:32:03.5+00

transaction_timestamp()

Description	Current date and time (start of current transaction).
Example	transaction_timestamp()
Result	2022-10-08 12:44:46.122-07

Timestamp with Time Zone Strings

TIMESTAMPTZ values are 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:

SQL

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

 timestamptz
------------------------
 2022-10-08 20:13:34+00

Named time zone parsing (such as parsing a time zone name from a string and casting it to a representation in the local time zone) uses the built-in time zone database.

Timestamp with Time Zone Operators

The table below shows the available mathematical operators for TIMESTAMP WITH TIME ZONE values. These operators work in the current time zone.

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.

Addition and subtraction of intervals works like 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.

Time Zone Aware Timestamp with Time Zone Functions

The table below shows the scalar functions for TIMESTAMP WITH TIME ZONE values that work in the current time zone and calendar.

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, starttimestamptz, endtimestamptz)	The number of part boundaries between starttimestamptz and endtimestamptz inclusive of the larger timestamp and exclusive of the smaller timestamp.
date_part([part, ...], timestamp)	Get the listed subfields as a struct. The list must be constant.
date_part(part, timestamp)	Get subfield (equivalent to extract).
date_sub(part, starttimestamptz, endtimestamptz)	The signed length of the interval between starttimestamptz and endtimestamptz, truncated to whole multiples of part.
date_trunc(part, timestamptz)	Truncate to specified precision.
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)	Parses string to a TIMESTAMP WITH TIME ZONE if a %Z element is present in the format, otherwise to a TIMESTAMP, according to the format string.
time_bucket(bucket_width, timestamptz[, offset])	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.
time_bucket(bucket_width, timestamptz[, origin])	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.
time_bucket(bucket_width, timestamptz[, timezone])	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.

age(timestamptz, timestamptz)

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

age(timestamptz)

Description	Subtract from current_date.
Example	age(TIMESTAMP '1992-09-20')
Result	29 years 1 month 27 days 12:39:00.844

date_diff(part, starttimestamptz, endtimestamptz)

Description	The signed number of part boundaries between starttimestamptz and endtimestamptz, inclusive of the larger timestamp and exclusive of the smaller timestamp.
Example	date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result	2

date_part([part, ...], timestamptz)

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}

date_part(part, timestamptz)

Description	Get subfield (equivalent to extract).
Example	date_part('minute', TIMESTAMPTZ '1992-09-20 20:38:40')
Result	38

date_sub(part, starttimestamptz, endtimestamptz)

Description	The signed length of the interval between starttimestamptz and endtimestamptz, truncated to whole multiples of part.
Example	date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
Result	1

date_trunc(part, timestamptz)

Description	Truncate to specified precision.
Example	date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
Result	1992-09-20 20:00:00

epoch_ns(timestamptz)

Converts a timestamptz to nanoseconds since the epoch.

SQL

SELECT epoch_ns('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ) AS ns;

 ns
---------------------
 1667810584123456000

epoch_us(timestamptz)

Converts a timestamptz to microseconds since the epoch.

SQL

SELECT epoch_us('2022-11-07 08:43:04.123456+00'::TIMESTAMPTZ) AS us;

 us
------------------
 1667810584123456

extract(field FROM timestamptz)

Description	Get subfield from a TIMESTAMP WITH TIME ZONE.
Example	extract('hour' FROM TIMESTAMPTZ '1992-09-20 20:38:48')
Result	20

last_day(timestamptz)

Description	The last day of the month.
Example	last_day(TIMESTAMPTZ '1992-03-22 01:02:03.1234')
Result	1992-03-31

make_timestamptz(bigint, bigint, bigint, bigint, bigint, double, string)

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

make_timestamptz(bigint, bigint, bigint, bigint, bigint, double)

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

make_timestamptz(microseconds)

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

strftime(timestamptz, format)

Converts a TIMESTAMP WITH TIME ZONE value to string according to the format string.

SQL

SELECT strftime(TIMESTAMPTZ '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p') AS formatted;

 formatted
-----------------------------------
 Wed, 1 January 1992 - 08:38:40 PM

strptime(text, format)

Parses string to a TIMESTAMP according to the format string. When the format contains a %Z element the result is a TIMESTAMP WITH TIME ZONE instead.

SQL

SELECT strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z') AS ts;

 ts
------------------------
 1992-01-01 16:38:40+00

time_bucket(bucket_width, timestamptz[, offset])

Description	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.
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

time_bucket(bucket_width, timestamptz[, origin])

Description	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.
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

time_bucket(bucket_width, timestamptz[, timezone])

Description	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.
Example	time_bucket(INTERVAL '2 days', TIMESTAMPTZ '1992-04-20 15:26:00-07', 'Europe/Berlin')
Result	1992-04-19 15:00:00-07 (=1992-04-20 00:00:00 Europe/Berlin)

There are also dedicated extraction functions to get the subfields.

Timestamp with Time Zone Table Functions

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

Name	Description
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.
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.

Tip

Infinite values are not allowed as table function bounds.

generate_series(timestamptz, timestamptz, interval)

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)

range(timestamptz, timestamptz, interval)

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)

Time Zone Aware Timestamp Without Time Zone Functions

The table below shows the scalar functions that operate on plain TIMESTAMP values and work with time zones. 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.
localtime	Synonym for the current_localtime() function call.
localtimestamp	Synonym 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.

current_localtime()

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

current_localtimestamp()

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

localtime

Description	Synonym for the current_localtime() function call.
Example	localtime
Result	08:47:56.497

localtimestamp

Description	Synonym for the current_localtimestamp() function call.
Example	localtimestamp
Result	2022-12-17 08:47:56.497

timezone(text, timestamp)

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

timezone(text, timestamptz)

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

At Time Zone

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

SQL

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver' AS ts;

 ts
------------------------
 2001-02-17 03:38:40+00

The input can also be spelled as a TIMESTAMP WITH TIME ZONE literal; the result is the local time in the given zone:

SQL

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver' AS ts;

 ts
---------------------
 2001-02-16 18:38:40

Numeric timezones are not allowed either:

SQL

SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE '0200' AS ts;

error Unknown TimeZone '0200'

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

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

Daylight Saving Time (DST) Transitions

Adding calendar intervals such as INTERVAL '1 day' to a TIMESTAMPTZ uses the time zone operators described above. The interval is added in terms of the calendar fields, so the result adjusts around a daylight saving time transition rather than simply adding a fixed number of hours:

SQL

SELECT (TIMESTAMPTZ '2025-03-29 02:30:00+01' + INTERVAL '1 day')           AT TIME ZONE 'Europe/Amsterdam' AS amsterdam_wall;

 amsterdam_wall
---------------------
 2025-03-30 03:30:00