Skip to main content
SereneDBSereneDB
897Contact Us

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.

OperatorDescriptionExampleResult
+addition of an INTERVALTIMESTAMP '1992-03-22 01:02:03' + INTERVAL 5 DAY1992-03-27 01:02:03
-subtraction of TIMESTAMPsTIMESTAMP '1992-03-27' - TIMESTAMP '1992-03-22'5 days
-subtraction of an INTERVALTIMESTAMP '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.

Scalar Timestamp Functions​

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

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

SQL
SELECT age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20') AS age;
 age
------------------------
 8 years 6 mons 20 days

age(timestamp)​

DescriptionSubtract from current_date.
Exampleage(TIMESTAMP '1992-09-20')
Result29 years 1 month 27 days 12:39:00.844

ago(interval)​

DescriptionSubtracts an interval from the current timestamp, returning a timestamp in the past. Equivalent to current_timestamp - interval.
Exampleago(INTERVAL 1 HOUR)
Result2024-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.

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

current_localtimestamp()​

DescriptionReturns the current timestamp with time zone (at the start of the transaction).
Examplecurrent_localtimestamp()
Result2024-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.

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

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

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

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

date_part(part, timestamp)​

Get subfield (equivalent to extract).

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

date_sub(part, starttimestamp, endtimestamp)​

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

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

date_trunc(part, timestamp)​

Truncate to specified precision.

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

dayname(timestamp)​

The (English) name of the weekday.

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

epoch_ms(timestamp)​

Returns the total number of milliseconds since the epoch.

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

epoch_ns(timestamp)​

Returns the total number of nanoseconds since the epoch.

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

epoch_us(timestamp)​

Returns the total number of microseconds since the epoch.

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

epoch(timestamp)​

Returns the total number of seconds since the epoch.

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

extract(field FROM timestamp)​

Get subfield from a timestamp.

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

greatest(timestamp, timestamp)​

The later of two timestamps.

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

isfinite(timestamp)​

Returns true if the timestamp is finite, false otherwise.

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

isinf(timestamp)​

Returns true if the timestamp is infinite, false otherwise.

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

julian(timestamp)​

Extract the Julian Day number from a timestamp.

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

last_day(timestamp)​

The last day of the month.

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

least(timestamp, timestamp)​

The earlier of two timestamps.

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

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

The timestamp for the given parts.

SQL
SELECT make_timestamp(1992, 9, 20, 13, 34, 27.123456) AS ts;
 ts
----------------------------
 1992-09-20 13:34:27.123456

make_timestamp(microseconds)​

Converts microseconds since the epoch to a timestamp.

SQL
SELECT make_timestamp(1667810584123456) AS ts;
 ts
----------------------------
 2022-11-07 08:43:04.123456

make_timestamp_ms(milliseconds)​

Converts milliseconds since the epoch to a timestamp.

SQL
SELECT make_timestamp_ms(1667810584123) AS ts;
 ts
-------------------------
 2022-11-07 08:43:04.123

make_timestamp_ns(nanoseconds)​

Converts nanoseconds since the epoch to a timestamp.

SQL
SELECT make_timestamp_ns(1667810584123456789) AS ts;
 ts
-------------------------------
 2022-11-07 08:43:04.123456789

monthname(timestamp)​

The (English) name of the month.

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

strftime(timestamp, format)​

Converts timestamp to string according to the format string.

SQL
SELECT strftime(TIMESTAMP '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-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.

SQL
SELECT strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S']) AS ts;
 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.

SQL
SELECT strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p') AS ts;
 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.

SQL
SELECT time_bucket(INTERVAL '10 minutes', TIMESTAMP '1992-04-20 15:26:00-07', INTERVAL '5 minutes') AS bucket;
 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.

SQL
SELECT time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00', TIMESTAMP '1992-04-01 00:00:00') AS bucket;
 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.

SQL
SELECT try_strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S']) AS ts;
 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.

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

Timestamp Table Functions​

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

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

generate_series(timestamp, timestamp, interval)​

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

SQL
SELECT * FROM generate_series(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) ORDER BY 1 LIMIT 3;
 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.

SQL
SELECT * FROM range(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) ORDER BY 1 LIMIT 3;
 range
---------------------
 2001-04-10 00:00:00
 2001-04-10 00:30:00
 2001-04-10 01:00:00
Edit this page ↗