Timestamp Functions
Version 0.9.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 `TIMESTAMP`s `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.

## Timestamp Functions

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

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`
`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`
`datediff(``part``, ``startdate``, ``enddate``)` Alias of date_diff. The number of partition boundaries between the timestamps `datediff('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`
`datepart(``part``, ``timestamp``)` Alias of date_part. Get subfield (equivalent to extract) `datepart('minute', TIMESTAMP '1992-09-20 20:38:40')` `38`
`date_part([``part``, ...], ``timestamp``)` Get the listed subfields as a `struct`. The list must be constant. `date_part(['year', 'month', 'day'], TIMESTAMP '1992-09-20 20:38:40')` `{year: 1992, month: 9, day: 20}`
`datepart([``part``, ...], ``timestamp``)` Alias of date_part. Get the listed subfields as a `struct`. The list must be constant. `datepart(['year', 'month', 'day'], TIMESTAMP '1992-09-20 20:38:40')` `{year: 1992, month: 9, day: 20}`
`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`
`datesub(``part``, ``startdate``, ``enddate``)` Alias of date_sub. The number of complete partitions between the timestamps `datesub('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`
`datetrunc(``part``, ``timestamp``)` Alias of date_trunc. Truncate to specified precision `datetrunc('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(``timestamp``)` Converts a timestamp to seconds since the epoch `epoch('2022-11-07 08:43:04'::TIMESTAMP);` `1667810584`
`epoch_ms(``timestamp``)` Converts a timestamp to milliseconds since the epoch `epoch_ms('2022-11-07 08:43:04.123456'::TIMESTAMP);` `1667810584123`
`epoch_ms(``ms``)` Converts ms since epoch to a timestamp `epoch_ms(701222400000)` `1992-03-22 00:00:00`
`epoch_ms(``timestamp``)` Return the total number of milliseconds since the epoch `epoch_ms(timestamp '2021-08-03 11:59:44.123456')` `1627991984123`
`epoch_us(``timestamp``)` Return the total number of microseconds since the epoch `epoch_ms(timestamp '2021-08-03 11:59:44.123456')` `1627991984123456`
`epoch_ns(``timestamp``)` Return the total number of nanoseconds since the epoch `epoch_ns(timestamp '2021-08-03 11:59:44.123456')` `1627991984123456000`
`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`
`isfinite(``timestamp``)` Returns true if the timestamp is finite, false otherwise `isfinite(TIMESTAMP '1992-03-07')` `true`
`isinf(``timestamp``)` Returns true if the timestamp is infinite, false otherwise `isinf(TIMESTAMP '-infinity')` `true`
`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`
`make_timestamp(``bigint``, ``bigint``, ``bigint``, ``bigint``, ``bigint``, ``double``)` The timestamp for the given parts `make_timestamp(1992, 9, 20, 13, 34, 27.123456)` `1992-09-20 13:34:27.123456`
`make_timestamp(``microseconds``)` The timestamp for the given number of µs since the epoch `make_timestamp(1667810584123456)` `2022-11-07 08:43:04.123456`
`monthname(``timestamp``)` The (English) name of the month. `monthname(TIMESTAMP '1992-09-20')` `September`
`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. Throws on failure. `strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')` `1992-01-01 20:38:40`
`strptime(``text``, ``format-list``)` Converts string to timestamp applying the format strings in the list until one succeeds. Throws on failure. `strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S'])` `2023-04-15 10:56:00`
`time_bucket(``bucket_width``, ``timestamp``[, ``origin``])` Truncate `timestamp` by the specified interval `bucket_width`. Buckets are aligned relative to `origin` timestamp. `origin` defaults to 2000-01-03 00:00:00 for buckets that don’t include a month or year interval, and to 2000-01-01 00:00:00 for month and year buckets. `time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00', TIMESTAMP '1992-04-01 00:00:00')` `1992-04-15 00:00:00`
`time_bucket(``bucket_width``, ``timestamp``[, ``offset``])` Truncate `timestamp` by the specified interval `bucket_width`. Buckets are offset by `offset` interval. `time_bucket(INTERVAL '10 minutes', TIMESTAMP '1992-04-20 15:26:00-07', INTERVAL '5 minutes')` `1992-04-20 15:25:00`
`to_timestamp(``double``)` Converts seconds since the epoch to a timestamp with time zone `to_timestamp(1284352323.5)` `2010-09-13 04:32:03.5+00`
`try_strptime(``text``, ``format``)` Converts string to timestamp according to the format string. Returns `NULL` on failure. `try_strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')` `1992-01-01 20:38:40`
`try_strptime(``text``, ``format-list``)` Converts string to timestamp applying the format strings in the list until one succeeds. Returns `NULL` on failure. `try_strptime('4/15/2023 10:56:00', ['%d/%m/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S'])` `2023-04-15 10:56:00`

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

## 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)`

Infinite values are not allowed as table function bounds.

Search Shortcut cmd + k | ctrl + k