Timestamp Functions
Version 0.3.2

This section describes functions and operators for examining and manipulating TIMESTAMP values.

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

Timestamp Functions

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

Function Description Example Result
age(timestamp, timestamp) Subtract arguments, resulting in the time difference between the two timestamps age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20') 8 years 6 months 20 days
age(timestamp) Subtract from current_date age(TIMESTAMP '1992-09-20') 29 years 1 month 27 days 12:39:00.844
century(timestamp) Extracts the century of a timestamp century(TIMESTAMP '1992-03-22') 20
current_timestamp Current date and time (start of current transaction)    
date_diff(part, startdate, enddate) The number of partition boundaries between the timestamps date_diff('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00') 2
date_part(part, timestamp) Get subfield (equivalent to extract) date_part('minute', TIMESTAMP '1992-09-20 20:38:40') 38
date_sub(part, startdate, enddate) The number of complete partitions between the timestamps date_sub('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01 01:58:00') 1
date_trunc(part, timestamp) Truncate to specified precision date_trunc('hour', TIMESTAMP '1992-09-20 20:38:40') 1992-09-20 20:00:00
dayname(timestamp) The (English) name of the weekday dayname(TIMESTAMP '1992-03-22') Sunday
epoch_ms(ms) Converts ms since epoch to a timestamp epoch_ms(701222400000) 1992-03-22 00:00:00
extract(field from timestamp) Get subfield from a timestamp extract('hour' FROM TIMESTAMP '1992-09-20 20:38:48') 20
greatest(timestamp, timestamp) The later of two timestamps greatest(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234') 1992-09-20 20:38:48
last_day(timestamp) The last day of the month. last_day(TIMESTAMP '1992-03-22 01:02:03.1234') 1992-03-31
least(timestamp, timestamp) The earlier of two timestamps least(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234') 1992-03-22 01:02:03.1234
monthname(timestamp) The (English) name of the month. monthname(TIMESTAMP '1992-09-20') September
now() Current date and time (start of current transaction)    
strftime(timestamp, format) Converts timestamp to string according to the format string strftime(timestamp '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p') Wed, 1 January 1992 - 08:38:40 PM
strptime(text, format) Converts string to timestamp according to the format string strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p') 1992-01-01 20:38:40
to_timestamp(sec) Converts sec since epoch to a timestamp to_timestamp(701222400) 1992-03-22 00:00:00

There are also dedicated extraction functions to get the subfields.

Timestamp Table Functions

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

Function Description Example
generate_series(timestamp, timestamp, interval) Generate a table of timestamps in the closed range, stepping by the interval generate_series(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE)
range(timestamp, timestamp, interval) Generate a table of timestamps in the half open range, stepping by the interval range(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE)