Timestamp Functions

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' INTERVAL 5 DAY
- 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 mons 20 days
age(timestamp) Subtract from current_date age(TIMESTAMP '1992-09-20') 26 years 9 mons 9 days
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
day(timestamp) Extracts the day of a timestamp day(TIMESTAMP '1992-03-22') 22
dayname(timestamp) The (English) name of the weekday dayname(TIMESTAMP '1992-03-22') Sunday
dayofweek(timestamp) Extracts the day of the week of a timestamp (0-6, 0 = Sunday, 6 = Saturday) dayofweek(TIMESTAMP '1992-03-22') 0
dayofyear(timestamp) Extracts the day of the year of a timestamp (1-366) dayofyear(TIMESTAMP '1992-03-22') 81
decade(timestamp) Extracts the decade of a timestamp decade(TIMESTAMP '1992-03-22') 199
epoch(timestamp) Extracts the epoch of a timestamp in seconds epoch(TIMESTAMP '1992-03-22') 701222400
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
hour(timestamp) Extracts the hour component of a timestamp hour(TIMESTAMP '1992-03-22 01:02:03.1234') 1
isodow(timestamp) Extracts the ISO day of the week of a timestamp (1-7, 1 = Monday, 7 = Sunday) isodow(TIMESTAMP '1992-03-22') 7
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
microsecond(timestamp) Extracts the sub-minute component of a timestamp in microseconds microsecond(TIMESTAMP '1992-03-22 01:02:03.1234') 3123400
millisecond(timestamp) Extracts the sub-minute component of a timestamp in milliseconds millisecond(TIMESTAMP '1992-03-22 01:02:03.1234') 3123
millenium(timestamp) Extracts the millenium of a timestamp millenium(TIMESTAMP '1992-03-22') 2
minute(timestamp) Extracts the minute component of a timestamp minute(TIMESTAMP '1992-03-22 01:02:03.1234') 2
month(timestamp) Extracts the month of a timestamp month(TIMESTAMP '1992-03-22') 3
monthname(timestamp) The (English) name of the month. monthname(TIMESTAMP '1992-09-20') March
now() Current date and time (start of current transaction)    
quarter(timestamp) Extracts the quarter of a timestamp quarter(TIMESTAMP '1992-03-22') 1
second(timestamp) Extracts the second component of a timestamp second(TIMESTAMP '1992-03-22 01:02:03.1234') 3
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
week(timestamp) Extracts the week number of a timestamp (1-53) week(TIMESTAMP '1992-03-22') 12
year(timestamp) Extracts the year of a timestamp year(TIMESTAMP '1992-03-22') 1992

Timestamp Table Functions

The table below shows the available scalar 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 range(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)