Timestamp Functions

This section describes functions and operators for examining and manipulating TIMESTAMP values. See also the related TIMESTAMPTZ functions.

Timestamp Operators

The table below shows the available mathematical operators for TIMESTAMP types.

Operator	Description	Example	Result
+	addition of an INTERVAL	TIMESTAMP '1992-03-22 01:02:03' + INTERVAL 5 DAY	1992-03-27 01:02:03
-	subtraction of TIMESTAMPs	TIMESTAMP '1992-03-27' - TIMESTAMP '1992-03-22'	5 days
-	subtraction of an INTERVAL	TIMESTAMP '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.

Scalar Timestamp Functions

The table below shows the available scalar functions for TIMESTAMP values.

Name	Description
age(timestamp, timestamp)	Subtract arguments, resulting in the time difference between the two timestamps.
age(timestamp)	Subtract from current_date.
ago(interval)	Subtracts an interval from the current timestamp.
century(timestamp)	Extracts the century of a timestamp.
current_localtimestamp()	Returns the current timestamp (at the start of the transaction).
date_diff(part, starttimestamp, endtimestamp)	The number of part boundaries between starttimestamp and endtimestamp, 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, starttimestamp, endtimestamp)	The signed length of the interval between starttimestamp and endtimestamp, truncated to whole multiples of part.
date_trunc(part, timestamp)	Truncate to specified precision.
dayname(timestamp)	The (English) name of the weekday.
epoch_ms(timestamp)	Returns the total number of milliseconds since the epoch.
epoch_ns(timestamp)	Returns the total number of nanoseconds since the epoch.
epoch_us(timestamp)	Returns the total number of microseconds since the epoch.
epoch(timestamp)	Returns the total number of seconds since the epoch.
extract(field FROM timestamp)	Get subfield from a timestamp.
greatest(timestamp, timestamp)	The later of two timestamps.
isfinite(timestamp)	Returns true if the timestamp is finite, false otherwise.
isinf(timestamp)	Returns true if the timestamp is infinite, false otherwise.
julian(timestamp)	Extract the Julian Day number from a timestamp.
last_day(timestamp)	The last day of the month.
least(timestamp, timestamp)	The earlier of two timestamps.
make_timestamp(bigint, bigint, bigint, bigint, bigint, double)	The timestamp for the given parts.
make_timestamp(microseconds)	Converts microseconds since the epoch to a timestamp.
make_timestamp_ms(milliseconds)	Converts milliseconds since the epoch to a timestamp.
make_timestamp_ns(nanoseconds)	Converts nanoseconds since the epoch to a timestamp.
monthname(timestamp)	The (English) name of the month.
strftime(timestamp, format)	Converts timestamp to string according to the format string.
strptime(text, format-list)	Converts the string text to timestamp applying the format strings in the list until one succeeds. Throws an error on failure. To return NULL on failure, use try_strptime.
strptime(text, format)	Converts the string text to timestamp according to the format string. Throws an error on failure. To return NULL on failure, use try_strptime.
time_bucket(bucket_width, timestamp[, offset])	Truncate timestamp to a grid of width bucket_width. The grid is anchored at 2000-01-01 00:00:00[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00[ + offset]. Note that 2000-01-03 is a Monday.
time_bucket(bucket_width, timestamp[, origin])	Truncate timestamp to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 00:00:00 when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00. Note that 2000-01-03 is a Monday.
try_strptime(text, format-list)	Converts the string text to timestamp applying the format strings in the list until one succeeds. Returns NULL on failure.
try_strptime(text, format)	Converts the string text to timestamp according to the format string. Returns NULL on failure.

There are also dedicated extraction functions to get the subfields.

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 date, the result will be NULL.

age(timestamp, timestamp)

Subtract arguments, resulting in the time difference between the two timestamps.

Query

SELECT age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20') AS age;

Result

 age------------------------ 8 years 6 mons 20 days

age(timestamp)

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

ago(interval)

Description	Subtracts an interval from the current timestamp, returning a timestamp in the past. Equivalent to current_timestamp - interval.
Example	ago(INTERVAL 1 HOUR)
Result	2024-11-30 12:28:48.895 (if current time is 2024-11-30 13:28:48.895)

century(timestamp)

Extracts the century of a timestamp.

Query

SELECT century(TIMESTAMP '1992-03-22') AS century;

Result

 century---------      20

current_localtimestamp()

Description	Returns the current timestamp with time zone (at the start of the transaction).
Example	current_localtimestamp()
Result	2024-11-30 13:28:48.895

date_diff(part, starttimestamp, endtimestamp)

The signed number of part boundaries between starttimestamp and endtimestamp, inclusive of the larger timestamp and exclusive of the smaller timestamp.

Query

SELECT date_diff('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00') AS diff;

Result

 diff------    2

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

Get the listed subfields as a struct. The list must be constant.

Query

SELECT date_part(['year', 'month', 'day'], TIMESTAMP '1992-09-20 20:38:40') AS parts;

Result

 parts------------- (1992,9,20)

date_part(part, timestamp)

Get subfield (equivalent to extract).

Query

SELECT date_part('minute', TIMESTAMP '1992-09-20 20:38:40') AS minute;

Result

 minute--------     38

date_sub(part, starttimestamp, endtimestamp)

The signed length of the interval between starttimestamp and endtimestamp, truncated to whole multiples of part.

Query

SELECT date_sub('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00') AS sub;

Result

 sub-----   1

date_trunc(part, timestamp)

Truncate to specified precision.

Query

SELECT date_trunc('hour', TIMESTAMP '1992-09-20 20:38:40') AS truncated;

Result

 truncated--------------------- 1992-09-20 20:00:00

dayname(timestamp)

The (English) name of the weekday.

Query

SELECT dayname(TIMESTAMP '1992-03-22') AS dayname;

Result

 dayname--------- Sunday

epoch_ms(timestamp)

Returns the total number of milliseconds since the epoch.

Query

SELECT epoch_ms(TIMESTAMP '2021-08-03 11:59:44.123456') AS ms;

Result

 ms--------------- 1627991984123

epoch_ns(timestamp)

Returns the total number of nanoseconds since the epoch.

Query

SELECT epoch_ns(TIMESTAMP '2021-08-03 11:59:44.123456') AS ns;

Result

 ns--------------------- 1627991984123456000

epoch_us(timestamp)

Returns the total number of microseconds since the epoch.

Query

SELECT epoch_us(TIMESTAMP '2021-08-03 11:59:44.123456') AS us;

Result

 us------------------ 1627991984123456

epoch(timestamp)

Returns the total number of seconds since the epoch.

Query

SELECT epoch('2022-11-07 08:43:04'::TIMESTAMP) AS seconds;

Result

 seconds------------ 1667810584

extract(field FROM timestamp)

Get subfield from a timestamp.

Query

SELECT extract('hour' FROM TIMESTAMP '1992-09-20 20:38:48') AS hour;

Result

 hour------   20

greatest(timestamp, timestamp)

The later of two timestamps.

Query

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

Result

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

isfinite(timestamp)

Returns true if the timestamp is finite, false otherwise.

Query

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

Result

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

isinf(timestamp)

Returns true if the timestamp is infinite, false otherwise.

Query

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

Result

 isinf------- t

julian(timestamp)

Extract the Julian Day number from a timestamp.

Query

SELECT julian(TIMESTAMP '1992-03-22 01:02:03.1234') AS julian;

Result

 julian------------------- 2448704.043091706

last_day(timestamp)

The last day of the month.

Query

SELECT last_day(TIMESTAMP '1992-03-22 01:02:03.1234') AS last_day;

Result

 last_day------------ 1992-03-31

least(timestamp, timestamp)

The earlier of two timestamps.

Query

SELECT least(TIMESTAMP '1992-03-22 01:02:03', TIMESTAMP '1993-08-14 08:22:33') AS least;

Result

 least--------------------- 1992-03-22 01:02:03

make_timestamp(bigint, bigint, bigint, bigint, bigint, double)

The timestamp for the given parts.

Query

SELECT make_timestamp(1992, 9, 20, 13, 34, 27.123456) AS ts;

Result

 ts---------------------------- 1992-09-20 13:34:27.123456

make_timestamp(microseconds)

Converts microseconds since the epoch to a timestamp.

Query

SELECT make_timestamp(1667810584123456) AS ts;

Result

 ts---------------------------- 2022-11-07 08:43:04.123456

make_timestamp_ms(milliseconds)

Converts milliseconds since the epoch to a timestamp.

Query

SELECT make_timestamp_ms(1667810584123) AS ts;

Result

 ts------------------------- 2022-11-07 08:43:04.123

make_timestamp_ns(nanoseconds)

Converts nanoseconds since the epoch to a timestamp.

Query

SELECT make_timestamp_ns(1667810584123456789) AS ts;

Result

 ts------------------------------- 2022-11-07 08:43:04.123456789

monthname(timestamp)

The (English) name of the month.

Query

SELECT monthname(TIMESTAMP '1992-09-20') AS monthname;

Result

 monthname----------- September

strftime(timestamp, format)

Converts timestamp to string according to the format string.

Query

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

Result

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

strptime(text, format-list)

Converts the string text to timestamp applying the format strings in the list until one succeeds. Throws an error on failure. To return NULL on failure, use try_strptime.

Query

SELECT strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S']) AS ts;

Result

 ts--------------------- 2023-04-15 10:56:00

strptime(text, format)

Converts the string text to timestamp according to the format string. Throws an error on failure. To return NULL on failure, use try_strptime.

Query

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

Result

 ts--------------------- 1992-01-01 20:38:40

time_bucket(bucket_width, timestamp[, offset])

Truncate timestamp to a grid of width bucket_width. The grid includes 2000-01-01 00:00:00[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00[ + offset]. Note that 2000-01-03 is a Monday.

Query

SELECT time_bucket(INTERVAL '10 minutes', TIMESTAMP '1992-04-20 15:26:00-07', INTERVAL '5 minutes') AS bucket;

Result

 bucket--------------------- 1992-04-20 15:25:00

time_bucket(bucket_width, timestamp[, origin])

Truncate timestamp to a grid of width bucket_width. The grid includes the origin timestamp, which defaults to 2000-01-01 00:00:00 when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00. Note that 2000-01-03 is a Monday.

Query

SELECT time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00', TIMESTAMP '1992-04-01 00:00:00') AS bucket;

Result

 bucket--------------------- 1992-04-15 00:00:00

try_strptime(text, format-list)

Converts the string text to timestamp applying the format strings in the list until one succeeds. Returns NULL on failure.

Query

SELECT try_strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S']) AS ts;

Result

 ts--------------------- 2023-04-15 10:56:00

try_strptime(text, format)

Converts the string text to timestamp according to the format string. Returns NULL on failure.

Query

SELECT try_strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p') AS ts;

Result

 ts--------------------- 1992-01-01 20:38:40

Timestamp Table Functions

The table below shows the available table functions for TIMESTAMP types.

Name	Description
generate_series(timestamp, timestamp, interval)	Generate a table of timestamps in the closed range, stepping by the interval.
range(timestamp, timestamp, interval)	Generate a table of timestamps in the half open range, stepping by the interval.

Tip

Infinite values are not allowed as table function bounds.

generate_series(timestamp, timestamp, interval)

Generate a table of timestamps in the closed range, stepping by the interval.

Query

SELECT * FROM generate_series(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) ORDER BY 1 LIMIT 3;

Result

 generate_series--------------------- 2001-04-10 00:00:00 2001-04-10 00:30:00 2001-04-10 01:00:00

range(timestamp, timestamp, interval)

Generate a table of timestamps in the half open range, stepping by the interval.

Query

SELECT * FROM range(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) ORDER BY 1 LIMIT 3;

Result

 range--------------------- 2001-04-10 00:00:00 2001-04-10 00:30:00 2001-04-10 01:00:00