MSQL Reference – Temporal Functions

The following are the built-in functions for working with temporal values (i.e. of timestamp | ts, timeperiod | tp or tsrange type).

Many temporal functions require the information about which time zone to use in the calculations. This can generally be specified as an optional timezone argument, but if it isn't specified, the session-default time zone is used. The session-default time zone is in general 'GMT', but can be modified using the SET statement (for all following statements until the end of the current SQL input).

Core Functions

fn CURRENT_TS

CURRENT_TS() -> timestamp

Returns the timestamp representing the current moment in time.

Notes

The current timestamp is time zone-independent.

Examples

SELECT CURRENT_TS();
  --> 1587374959::timestamp

See also

fn CURRENT_TZ

CURRENT_TZ() -> text

Returns the name of the session-default time zone.

Examples

SELECT CURRENT_TZ();
  --> 'GMT'

See also

CURRENT_TS

fn PARSE_EPOCH

PARSE_EPOCH(period_str: text) -> unixepoch

Parses a unixepoch from a given datetime string.

Arguments

If the optional timezone argument is given, the datetime string is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If the argument is NULL, the function will also return NULL.

Error handling

If the datetime_str doesn't match the expected format, the function will return an error: Malformed timestamp string.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

The expected format of the given string is 'YYYY-mm-dd HH:MM:SS.fff' (the same as for parse_ts-function function). Not all parts of the format need to be present, only a valid prefix is required. In that case, the unspecified fields are truncated to the lowest value (1 for months and days, 0 for hours, minutes, seconds and fractions of second).

As unixepoch values have a precision of one second, if the fractional part is present, it will be discarded.

The result might not fit into a 32-bit unsigned integer representing an unixepoch value, in which case it will overflow/underflow. The available range of the unixepoch values is '1970-01-01 00:00:00' - '2106-02-07 06:28:15'.

Examples

SELECT PARSE_EPOCH('2020-04-21 11:36:43.125');
  --> 1587469003::unixepoch

SELECT PARSE_EPOCH('2020-04-21 11:36:43.125', 'America/Los_Angeles');
  --> 1587494203::unixepoch

fn PARSE_TP

PARSE_TP(period_str: text) -> timeperiod

Parses a timeperiod from a given time-period string.

Null handling

If the argument is NULL, the function will also return NULL.

Error handling

If the period_str doesn't match the expected format, the function will return an error: Malformed timeperiod string.

Notes

The expected format of the given string is '[-]N1 unit1 [N2 unit2 ...]', where N_i are integers, and unit_i are names/shortcut names of time units (see the TS_TRUNC documentation for a list of valid time units for timeperiods).

The result might not fit into a 64-bit integer representing a timeperiod value, in which case it will overflow/underflow. But this should never actually happen in practice because the timeperiod value span is from ~292 million years to ~292 million years.

Examples

SELECT PARSE_TP('2 hours 15 minutes');
  --> 8100::timeperiod

SELECT PARSE_TP('5h 37m 12s');
  --> 20232

See also

timeperiod | tp PARSE_TS

fn PARSE_TS

PARSE_TS(datetime_str: text) -> timestamp

PARSE_TS(datetime_str: text, timezone: text) -> timestamp

Parses a timestamp from a given datetime string.

Arguments

If the optional timezone argument is given, the datetime string is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If the datetime_str doesn't match the expected format, the function will return an error: Malformed timestamp string.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

The expected format of the given string is 'YYYY-mm-dd HH:MM:SS.fff'. Not all parts of the format need to be present, only a valid prefix is required. In that case, the unspecified fields are truncated to the lowest value (1 for months and days, 0 for hours, minutes, seconds and fraction).

Examples

SELECT PARSE_TS('2020-04-21 11:36:43.125');
  --> 1587469003.125::timestamp

SELECT PARSE_TS('2020-04-21 11:36:43.125', 'America/Los_Angeles');
  --> 1587494203.125::timestamp

See also

timestamp | ts PARSE_TSRANGE PARSE_TP

fn PARSE_TSRANGE

PARSE_TSRANGE(datetime_str: text) -> rtimestamp

PARSE_TSRANGE(datetime_str: text, timezone: text) -> rtimestamp

Parses a rtimestamp from a given datetime-range string.

Arguments

If the optional timezone argument is given, the datetime string is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If the datetime_str doesn't match the expected format, the function will return an error: Malformed timestamp string.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

The expected format of the given string is '[YYYY-mm-dd HH:MM:SS.fff, YYYY-mm-dd HH:MM:SS.fff]'. Not all parts of the individual timestamp format need to be present, only a valid prefix is required. In that case, the unspecified fields are truncated to the lowest value (1 for months and days, 0 for hours, minutes and seconds).

fn PARSE_EPOCHRANGE

PARSE_EPOCHRANGE(datetime_str: text) -> runixepoch

PARSE_EPOCHRANGE(datetime_str: text, timezone: text) -> runixepoch

Parses a runixepoch from a given datetime-range string.

Arguments

If the optional timezone argument is given, the datetime string is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If the datetime_str doesn't match the expected format, the function will return an error: Malformed timestamp string.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

fn TO_CHAR

TO_CHAR(ts: timestamp) -> text

TO_CHAR(ts: timestamp, format: text) -> text

TO_CHAR(ts: timestamp, format: text, timezone: text) -> text

TO_CHAR(tp: timeperiod) -> text

TO_CHAR(tp: timeperiod, format: text) -> text

TO_CHAR(rts: rtimestamp) -> text

TO_CHAR(rts: rtimestamp, format: text) -> text

TO_CHAR(rts: rtimestamp, format: text, timezone: text) -> text

Returns the textual representation of a given timestamp or timeperiod.

Arguments

The optional second parameter, format, represents a custom output format to be used. If the format argument isn't specified, the default value for timestamps is '%Y-%m-%d %H:%M:%S' and for timeperiods '%p%dd %Hh %Mm %Ss %fms'.

The optional third parameter for timestamp conversion, timezone, is a time zone name (e.g. 'Europe/Berlin'), which declares that the timestamp should be treated as belonging to the specified time zone (as opposed to the session-default time zone).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an unsupported modifier is specified in the format string, the function will return an error: Unknown format specifier.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

All the available format specifiers for formatting timestamp values are listed in the table below:

Specifier	Meaning (for timestamps)	Example
`%w`	Weekday as a decimal number (0 - Sunday, 6 - Saturday)	`0, 1, ..., 6`
`%u`	Weekday as a decimal number (1 - Monday, 7 - Sunday)	`1, 2, ..., 7`
`%d`	Day of the month as a zero-padded decimal number	`01, 02, ..., 31`
`%m`	Month as a zero-padded decimal number	`01, 02, ..., 12`
`%y`	Year without century as a zero-padded decimal number	`00, 01, ..., 99`
`%Y`	Year with century as a decimal number	`0001, ..., 2020, ..., 9999`
`%H`	Hour (24-hour clock) as a zero-padded decimal number	`00, 01, ..., 23`
`%I`	Hour (12-hour clock) as a zero-padded decimal number	`01, 02, ..., 12`
`%p`	AM/PM	`AM, PM`
`%M`	Minute as a zero-padded decimal number	`00, 01, ..., 59`
`%S`	Second as a zero-padded decimal number	`00, 01, ..., 59`
`%f`	Microsecond as a decimal number, zero-left-padded	`000000, 000001, ..., 999999`
`%z`	UTC offset in the form `+/-HHMM`	`+0000, -0400, +1030`
`%Z`	Time zone name	`UTC, Europe/London`
`%j`	Day of the year as a zero-padded decimal number	`001, 002, ..., 366`
`%U`	Week number of the year ¹ (Sunday as the first weekday)	`00, 01, ..., 53`
`%W`	ISO 8601 week number of the year (Monday as the first weekday)*	`00, 01, ..., 53`
`%%`	A literal `%` character	`%`

[1]: All days in a new year preceding the first Sunday/Monday are considered to be in week 0.

For timeperiod values, the available format specifiers are the following:

Specifier	Meaning	Example
`%p`	Sign of the `timeperiod` value if it's negative, otherwise nothing	`-, ''`
`%P`	Sign of the `timeperiod` value (always shown)	`+, -`
`%y`	Years¹	`1, 2, ..., N`
`%m`	Months²	`1, 2, ..., N`
`%w`	Weeks³	`1, 2, ..., N`
`%d`	Days	`1, 2, ..., N`
`%H`	Hours	`1, 2, ..., N`
`%M`	Minutes	`1, 2, ..., N`
`%S`	Seconds	`1, 2, ..., N`
`%f`	Microseconds	`1, 2, ..., N`
`%%`	A literal `%` character	`%`

[1]: A year is defined as a period of 365.2425 days.

[2]: A month is defined as a period of 1/12 of a year.

[3]: A week is defined as a period of 7 days.

In the timeperiod format string, the value for each specifier depends on the context.
If a higher specifier is present (e.g. '%d', days), the value of the lower specifier (e.g. '%H', hours) will be calculated from what remains when the value of the higher specifier is subtracted from the timeperiod. For example, TO_CHAR(128732::timeperiod, '%Hh') returns '35h', but TO_CHAR(128732::timeperiod, '%dd, %Hh') returns '1d 11h'.

Examples

SELECT TO_CHAR(timestamp '2020-04-20 11:20:00');
  --> '2020-04-20 11:20:00.000000'

SELECT TO_CHAR(645::timeperiod);
  --> '0d 0h 10m 45s 0us'

SELECT TO_CHAR(timestamp '2020-04-20 11:20:00', '%d.%m.%Y.');
  --> '20.04.2020.'

SELECT TO_CHAR(645::timeperiod, '%M minutes, %S seconds');
  --> '10 minutes, 45 seconds'

fn TS_ADDM

TS_ADDM(ts: timestamp, months: int64) -> timestamp

TS_ADDM(ts: timestamp, months: int64, timezone: text) -> timestamp

Adds a number of months to a timestamp and returns the result.

Arguments

If the optional timezone argument is given, the given timestamp is treated as if specified in that particular time zone (and not the session-default one). This argument is available because the operation is timezone-dependent due to the fact that the same UTC timestamp might correspond to different days (or months) in different timezones (e.g. '2020-03-01 02:00:00 Australia/Sydney' is '2020-02-28 15:00:00 Europe/London').

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

If the result of the addition is not a valid date (e.g. TS_ADDM(TS '2020-01-31', 1) would result in '2020-02-31'), the result will be adjusted to the last valid date of the month.

The result of this function might not fit into a 64-bit integer representing a timestamp value, in which case it will overflow/underflow, but this will never happen in practice because the timestamp value span is from ~292 million years BC to ~292 million years AD.

The TS_ADDM function is useful because the same cannot be accomplished using the timeperiod addition, as months (and years) don't have a fixed duration in seconds.

Examples

SELECT TS_ADDM(timestamp '2020-02-15 08:15:00', 3);
  --> '2020-05-15 08:15:00.000000'::timestamp

SELECT TS_ADDM(timestamp '2020-02-29 08:15:00', 12);
  --> '2021-02-28 08:15:00.000000'::timestamp

See also

TS_SUBM

fn TS_BUCKET

TS_BUCKET(bucket_width: timeperiod, ts: timestamp) -> timestamp

TS_BUCKET(bucket_width: timeperiod, ts: timestamp, origin: timestamp) -> timestamp

Groups timestamps into buckets of a given size, i.e. returns the timestamp of the start of the corresponding bucket.

Arguments

If the optional origin argument is given, the beginnings of all buckets will be aligned with it. The value of the origin argument can be in the future. When origin isn't specified, the default value is 2000-01-03 00:00:00.000 (the first Monday of the year 2000).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If the bucket_width is zero, the function will return an error: Division by zero.

If the bucket_width is negative, the function will return an error: Function argument bucket_width must not be negative.

Notes

The result of this function might not fit into a 64-bit integer representing a timestamp value, in which case it will underflow, but this will never happen in practice because the timestamp value span is from ~292 million years BC to ~292 million years AD.

This function is a more powerful version of TS_TRUNC. It can be used to group records into buckets of e.g. 15 minutes, 1.5 hours, 3 days.

Examples

SELECT TS_BUCKET(timeperiod '10s', t)::text AS to_10sec, TS_BUCKET(timeperiod '1h 30min', t)::text AS to_90min
 FROM (VALUES timestamp '2020-04-15 07:46:34') AS t(t);

--> +------------------------------+------------------------------+
--> |                     to_10sec |                     to_90min |
--> +------------------------------+------------------------------+
--> | '2020-04-15 07:46:30.000000' | '2020-04-15 07:30:00.000000' |
--> +------------------------------+------------------------------+

SELECT TS_BUCKET(timeperiod '10s', t1, t2)::text AS to_10sec, TS_BUCKET(timeperiod '1h 30min', t1, t2)::text AS to_90min
 FROM (VALUES (timestamp '2020-04-15 07:46:34', timestamp '2020-04-15 05:00:05')) AS t(t1, t2);

--> +------------------------------+------------------------------+
--> |                     to_10sec |                     to_90min |
--> +------------------------------+------------------------------+
--> | '2020-04-15 07:46:25.000000' | '2020-04-15 06:30:05.000000' |
--> +------------------------------+------------------------------+

See also

TS_TRUNC

fn TS_DATE

TS_DATE(year: int64, month: int64, day: int64) -> timestamp

TS_DATE(year: int64, month: int64, day: int64, timezone: text) -> timestamp

Constructs a timestamp corresponding to the given date year/month/day.

Arguments

If the optional timezone argument is given, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an invalid date is given (e.g. February 31st), the function will return an error: Invalid date.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

Examples

SELECT TS_DATE(2020, 3, 15);
  --> '2020-03-15 00:00:00.000000'::timestamp

See also

TS_DATETIME PARSE_TS

fn TS_DATETIME

TS_DATETIME(year: int64, month: int64, day: int64, hour: int64, minute: int64, second: int64, nanosecond: int64) -> timestamp

TS_DATETIME(year: int64, month: int64, day: int64, hour: int64, minute: int64, second: int64, nanosecond: int64, timezone: text) -> timestamp

Constructs a timestamp corresponding to the given date-time year/month/day hour:minute:second.nanosecond.

Arguments

If the optional timezone argument is given, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an invalid date is given (e.g. February 31st), the function will return an error: Invalid date.

If an invalid time is given (e.g. 26:30:00), the function will return an error: Invalid time.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

Examples

SELECT TS_DATETIME(2020, 3, 15, 8, 27, 32, 0);
  --> '2020-03-15 08:27:32.000000'::timestamp

See also

TS_DATE PARSE_TS

fn TS_EXTRACT

TS_EXTRACT(timepart: text, ts: timestamp) -> int64

TS_EXTRACT(timepart: text, ts: timestamp, timezone: text) -> int64

TS_EXTRACT(timepart: text, tp: timeperiod) -> int64

Extracts the specified part of a timestamp or a timeperiod.

Arguments

For the timestamp variant, if the optional timezone argument is given, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an unsupported time part is specified, the function will return an error: Unknown timepart.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

Parts (i.e. timepart argument values) which can be extracted are the following (both singular and plural names are allowed, as well as some shortcuts):

Part	Meaning (for timestamps)	Meaning (for timeperiods)
`YEAR`, `YEARS`, `Y`, `YR`	Year	Approximate number of years in the period¹
`MONTH`, `MONTHS`, `MO`	Month of the year (1-12)	Approximate number of months in the period²
`DAY`, `DAYS`, `D`	Day of the month (1-31)	Number of days in the period
`HOUR`, `HOURS`, `H`, `HR`	Hour (0-24)	Number of hours in the period
`MINUTE`, `MINUTES`, `M`, `MIN`	Minute (0-59)	Number of minutes in the period
`SECOND`, `SECONDS`, `S`, `SEC`	Second (0-59)	Number of seconds in the period
`MILLISECOND`, `MILLISECONDS`, `MS`	Millisecond (0-999)	Number of milliseconds in the period
`MICROSECOND`, `MICROSECONDS`, `US`	Microsecond (0-999999)	Number of microseconds in the period
`NANOSECOND`, `NANOSECONDS`, `NS`	Nanosecond (0-999999999)	Number of nanoseconds in the period
`WEEK`, `WEEKS`, `W`, `WK`	Week of the year (Sunday as the first weekday) (0-53)	Number of weeks in the period³
`ISOWEEK`	Week of the year (ISO 8601, Monday as the first weekday) (0-53)	n/a
`DOW`	Weekday (Sun 0 - Sat 6)	n/a
`ISODOW`	Weekday (ISO 8601) (Mon 1 - Sun 7)	n/a
`DOY`	Day of the year (1-366)	n/a

[1]: For timeperiods, a year is defined as a period of 365.2425 days.

[2]: For timeperiods, a month is defined as a period of 1/12 of a year.

[3]: For timeperiods, a week is defined as a period of 7 days.

Examples

SELECT TS_EXTRACT('year', t) AS year, TS_EXTRACT('month', t) AS month, TS_EXTRACT('day', t) AS day,
    TS_EXTRACT('hour', t) AS hour, TS_EXTRACT('minutes', t) AS minutes, TS_EXTRACT('seconds', t) AS seconds,
    TS_EXTRACT('week', t) AS week, TS_EXTRACT('isoweek', t) AS isoweek,
    TS_EXTRACT('dow', t) AS dow, TS_EXTRACT('isodow', t) AS isodow, TS_EXTRACT('doy', t) AS doy
  FROM (VALUES timestamp '2020-04-15 08:15:34') AS t(t);

--> +------+-------+-----+------+---------+---------+------+---------+-----+--------+-----+
--> | year | month | day | hour | minutes | seconds | week | isoweek | dow | isodow | doy |
--> +------+-------+-----+------+---------+---------+------+---------+-----+--------+-----+
--> | 2020 |     4 |  15 |    8 |      15 |      34 |   15 |      16 |   3 |      3 | 106 |
--> +------+-------+-----+------+---------+---------+------+---------+-----+--------+-----+

SELECT TS_EXTRACT('hours', t, 'America/Los_Angeles') AS `Los Angeles`, TS_EXTRACT('hours', t, 'Europe/London') AS `London`
  FROM (VALUES 1586937600::timestamp) AS t(t);

--> +-------------+--------+
--> | Los Angeles | London |
--> +-------------+--------+
--> |           1 |      9 |
--> +-------------+--------+

SELECT TS_EXTRACT('days', p) AS days, TS_EXTRACT('hours', p) AS hours, TS_EXTRACT('minutes', p) AS minutes, TS_EXTRACT('seconds', p) AS seconds
  FROM (VALUES timeperiod '1 day 1 hour 9 minutes 22 seconds') AS t(p);

--> +--------+--------+-----------+---------+
--> |   days |  hours |   minutes | seconds |
--> +--------+--------+-----------+---------+
--> | 1.0482 | 25.156 | 1509.3667 |   90562 |
--> +--------+--------+-----------+---------+

fn TS_SUBM

TS_SUBM(ts: timestamp, months: int64) -> timestamp

TS_SUBM(ts: timestamp, months: int64, timezone: text) -> timestamp

Subtracts a number of months from a timestamp and returns the result.

Arguments

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

If the result of the subtraction is not a valid date (e.g. TS_SUBM(TS('2020-03-31'), 1) would result in '2020-02-31'), the result will be adjusted to the last valid date of the month.

The TS_SUBM function is useful because the same cannot be accomplished using the timeperiod subtraction, as months (and years) don't have a fixed duration in seconds.

Examples

SELECT TS_SUBM(timestamp '2020-02-15 08:15:00', 3);
  --> '2019-11-15 08:15:00.000000'::timestamp

SELECT TS_SUBM(timestamp '2020-04-15 08:15:00', 12);
  --> '2019-04-15 08:15:00.000000'::timestamp

See also

TS_ADDM

fn TS_TRUNC

TS_TRUNC(timeunit: text, ts: timestamp) -> timestamp

TS_TRUNC(timeunit: text, ts: timestamp, timezone: text) -> timestamp

TS_TRUNC(timeunit: text, tp: timeperiod) -> timeperiod

Truncates a timestamp or timeperiod to a precision of a given time unit.

Arguments

For the timestamp variant, if the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If an unsupported time unit is specified, the function will return an error: Unknown timeunit.

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

The following units (i.e. timeunit argument values) are supported (both singular and plural names are allowed, as well as some shortcuts):

Unit	Meaning (for timestamps)	Meaning (for timeperiods)
`YEAR`, `YEARS`, `Y`, `YR`	Truncate to 1st day of the year	Truncate to whole number of years¹
`MONTH`, `MONTHS`, `MO`	Truncate to 1st day of the month	Truncate to whole number of months²
`DAY`, `DAYS`, `D`	Truncate to midnight of the day	Truncate to whole number of days
`HOUR`, `HOURS`, `H`, `HR`	Truncate to start of the hour	Truncate to whole number of hours
`MINUTE`, `MINUTES`, `M`, `MIN`	Truncate to start of the minute	Truncate to whole number of minutes
`SECOND`, `SECONDS`, `S`, `SEC`	Truncate to start of the second	Truncate to whole number of seconds
`MILLISECOND`, `MILLISECONDS`, `MS`	Truncate to start of the millisecond (no-op)	Truncate to whole number of milliseconds (no-op)
`WEEK`, `WEEKS`, `W`, `WK`	Truncate to start of the week (Sunday)	Truncate to whole number of weeks³
`ISOWEEK`	Truncate to start of the week (Monday)	n/a

[1]: For timeperiods, a year is defined as a period of 365.2425 days.

[2]: For timeperiods, a month is defined as a period of 1/12 of a year.

[3]: For timeperiods, a week is defined as a period of 7 days.

Examples

SELECT TS_TRUNC('hour', t)::text AS to_hour, TS_TRUNC('day', t)::text AS to_day, TS_TRUNC('month', t)::text AS to_month
  FROM (VALUES timestamp '2020-04-15 08:15:42') AS t(t);

--> +------------------------------+------------------------------+------------------------------+
--> |                      to_hour |                       to_day |                     to_month |
--> +------------------------------+------------------------------+------------------------------+
--> | '2020-04-15 08:00:00.000000' | '2020-04-15 00:00:00.000000' | '2020-04-01 00:00:00.000000' |
--> +------------------------------+------------------------------+------------------------------+

SELECT TS_TRUNC('day', t, 'America/Los_Angeles'), TS_TRUNC('day', t, 'Europe/Berlin')
  FROM (VALUES 1586926800::timestamp) AS t(t);

--> +-------------+------------+
--> | Los Angeles |     Berlin |
--> +-------------+------------+
--> |  1586847600 | 1586901600 |
--> +-------------+------------+

SELECT TS_TRUNC('minutes', p)::text, TS_TRUNC('hours', p)::text, TS_TRUNC('days', p)::text
  FROM (VALUES 286732::timeperiod) AS t(p);

--> +--------------------+-------------------+-------------------+
--> |            minutes |             hours |              days |
--> +--------------------+-------------------+-------------------+
--> | '3d 7h 38m 0s 0us' | '3d 7h 0m 0s 0us' | '3d 0h 0m 0s 0us' |
--> +--------------------+-------------------+-------------------+

Utility functions

fn TS_DAYSTART

TS_DAYSTART(ts: timestamp) -> timestamp

TS_DAYSTART(ts: timestamp, timezone: text) -> timestamp

Truncates the given timestamp to the start of the day.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_TRUNC, i.e. TS_TRUNC('day', ts).

Examples

SELECT TS_DAYSTART(timestamp '2021-01-17 08:32:47');
  --> 1610841600::timestamp

SELECT TS_DAYSTART(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 1609401600::timestamp

fn TS_WEEKSTART

TS_WEEKSTART(ts: timestamp) -> timestamp

TS_WEEKSTART(ts: timestamp, timezone: text) -> timestamp

Truncates the given timestamp to the start of the week (Sunday).

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_TRUNC, i.e. TS_TRUNC('week', ts).

Examples

SELECT TS_WEEKSTART(timestamp '2021-01-17 08:32:47');
  --> 1610841600::timestamp

SELECT TS_WEEKSTART(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 1609056000::timestamp

fn TS_ISOWEEKSTART

TS_ISOWEEKSTART(ts: timestamp) -> timestamp

TS_ISOWEEKSTART(ts: timestamp, timezone: text) -> timestamp

Truncates the given timestamp to the start of the ISO week (Monday).

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_TRUNC, i.e. TS_TRUNC('isoweek', ts).

Examples

SELECT TS_ISOWEEKSTART(timestamp '2021-01-17 08:32:47');
  --> 1610323200::timestamp

SELECT TS_ISOWEEKSTART(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 1609142400::timestamp

fn TS_MONTHSTART

TS_MONTHSTART(ts: timestamp) -> timestamp

TS_MONTHSTART(ts: timestamp, timezone: text) -> timestamp

Truncates the given timestamp to the start of the month.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_TRUNC, i.e. TS_TRUNC('month', ts).

Examples

SELECT TS_MONTHSTART(timestamp '2021-01-17 08:32:47');
  --> 1609459200::timestamp

SELECT TS_MONTHSTART(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 1606809600::timestamp

fn TS_YEARSTART

TS_YEARSTART(ts: timestamp) -> timestamp

TS_YEARSTART(ts: timestamp, timezone: text) -> timestamp

Truncates the given timestamp to the start of the week (Sunday).

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_TRUNC, i.e. TS_TRUNC('year', ts).

Examples

SELECT TS_YEARSTART(timestamp '2021-01-17 08:32:47');
  --> 1609459200::timestamp

SELECT TS_YEARSTART(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 1577865600::timestamp

fn TS_TIMEOFDAY

TS_TIMEOFDAY(ts: timestamp) -> timeperiod

TS_TIMEOFDAY(ts: timestamp, timezone: text) -> timeperiod

Returns the time passed since the start of the day for a given timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This function is equivalent to calling ts - TS_TRUNC('day', ts).

Examples

SELECT TS_TIMEOFDAY(timestamp '2021-01-17 08:32:47');
  --> 30767::timeperiod

SELECT TS_TIMEOFDAY(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 72000::timeperiod

See also

TS_TRUNC TS_DAYSTART TODAY YESTERDAY

fn NOW

NOW() -> timestamp

Alias for the CURRENT_TS function.

See also

TODAY YESTERDAY

fn TODAY

TODAY() -> timestamp

TODAY(timezone: text) -> timestamp

Returns the timestamp of the start of the current day.

Arguments

If the optional timezone argument is supplied, the result is the start of the current day in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Examples

SELECT TODAY();
  --> 1631232000000::timestamp

SELECT TODAY('America/Los_Angeles');
  --> 1631257200000::timestamp

See also

CURRENT_TS YESTERDAY TS_DAYSTART

fn YESTERDAY

YESTERDAY() -> timestamp

YESTERDAY(timezone: text) -> timestamp

Returns the timestamp of the start of the previous day.

Arguments

If the optional timezone argument is supplied, the result is the start of the previous day in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Examples

SELECT YESTERDAY();
  --> 1631257200000::timestamp

SELECT YESTERDAY('America/Los_Angeles');
  --> 1631170800000::timestamp

See also

CURRENT_TS TODAY

fn YEAR

YEAR(ts: timestamp) -> int64

YEAR(ts: timestamp, timezone: text) -> int64

Extracts the year value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('year', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT YEAR(timestamp '2021-01-17 08:32:47');
  --> 2021

SELECT YEAR(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 2020

fn MONTH

MONTH(ts: timestamp) -> int64

MONTH(ts: timestamp, timezone: text) -> int64

Extracts the month value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('month', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MONTH(timestamp '2021-01-17 08:32:47');
  --> 1

SELECT MONTH(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 12

fn DAY

DAY(ts: timestamp) -> int64

DAY(ts: timestamp, timezone: text) -> int64

Extracts the day of the month value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('day', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT DAY(timestamp '2021-01-17 08:32:47');
  --> 17

SELECT DAY(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 31

fn HOUR

HOUR(ts: timestamp) -> int64

HOUR(ts: timestamp, timezone: text) -> int64

Extracts the hour value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('hour', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT HOUR(timestamp '2021-01-17 08:32:47');
  --> 8

SELECT HOUR(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 20

fn MINUTE

MINUTE(ts: timestamp) -> int64

MINUTE(ts: timestamp, timezone: text) -> int64

Extracts the minutes value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('minute', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MINUTE(timestamp '2021-01-17 08:32:47');
  --> 32

SELECT MINUTE(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 0

fn SECOND

SECOND(ts: timestamp) -> int64

SECOND(ts: timestamp, timezone: text) -> int64

Extracts the seconds value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('second', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT SECOND(timestamp '2021-01-17 08:32:47');
  --> 47

SELECT SECOND(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 0

fn MILLISECOND

MILLISECOND(ts: timestamp) -> int64

MILLISECOND(ts: timestamp, timezone: text) -> int64

Extracts the milliseconds value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('millisecond', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MILLISECOND(timestamp '2021-01-17 08:32:47.541');
  --> 541

SELECT MILLISECOND(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 0

fn WEEK

WEEK(ts: timestamp) -> int64

WEEK(ts: timestamp, timezone: text) -> int64

Extracts the week value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('week', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT WEEK(timestamp '2021-01-17 08:32:47');
  --> 2

SELECT WEEK(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 52

fn ISOWEEK

ISOWEEK(ts: timestamp) -> int64

ISOWEEK(ts: timestamp, timezone: text) -> int64

Extracts the ISO week value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('isoweek', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT ISOWEEK(timestamp '2021-01-17 08:32:47');
  --> 2

SELECT ISOWEEK(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 53

fn DOW

DOW(ts: timestamp) -> int64

DOW(ts: timestamp, timezone: text) -> int64

Extracts the day-of-week value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('dow', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT DOW(timestamp '2021-01-17 08:32:47');
  --> 0

SELECT DOW(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 4

fn ISODOW

ISODOW(ts: timestamp) -> int64

ISODOW(ts: timestamp, timezone: text) -> int64

Extracts the ISO day-of-week value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('isodow', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT ISODOW(timestamp '2021-01-17 08:32:47');
  --> 7

SELECT ISODOW(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 4

fn DOY

DOY(ts: timestamp) -> int64

DOY(ts: timestamp, timezone: text) -> int64

Extracts the day-of-year value from a timestamp.

Arguments

If the optional timezone argument is supplied, the given timestamp is treated as if specified in that particular time zone (and not the session-default one).

Error handling

If an incorrect/unknown time zone name is specified, the function will return an error: Unknown timezone.

Notes

This is just a shortcut for calling TS_EXTRACT('doy', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT DOY(timestamp '2021-01-17 08:32:47');
  --> 17

SELECT DOY(timestamp '2021-01-01 04:00', 'America/Los_Angeles');
  --> 366

fn YEARS

YEARS(num: float64) -> timeperiod

YEARS(tp: timeperiod) -> f64

Returns a timeperiod of num years (a year is defined as a period of 365.2425 days), or extracts years from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('years', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT YEARS(3);
  --> 94670856

SELECT YEARS(1.5);
  --> 47335428

SELECT YEARS(timeperiod '3 years 7 months');
  --> 3.58

fn MONTHS

MONTHS(num: float64) -> timeperiod

MONTHS(tp: timeperiod) -> f64

Returns a timeperiod of num months (a month is defined as 1/12th of a year), or extracts months from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('months', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MONTHS(3);
  --> 7889238

SELECT MONTHS(1.5);
  --> 3944619

SELECT MONTHS(timeperiod '4 months 13 days');
  --> 4.43

fn WEEKS

WEEKS(num: float64) -> timeperiod

WEEKS(tp: timeperiod) -> f64

Returns a timeperiod of num weeks (a week is defined as a period of 7 days), or extracts weeks from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('weeks', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT WEEKS(3);
  --> 1814400

SELECT WEEKS(1.5);
  --> 907200

SELECT WEEKS(timeperiod '2 weeks 4 days');
  --> 2.57

fn DAYS

DAYS(num: float64) -> timeperiod

DAYS(tp: timeperiod) -> f64

Returns a timeperiod of num days, or extracts days from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('days', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT DAYS(3);
  --> 259200

SELECT DAYS(1.5);
  --> 129600

SELECT DAYS(timeperiod '5 days 17 hours');
  --> 5.71

fn HOURS

HOURS(num: float64) -> timeperiod

HOURS(tp: timeperiod) -> f64

Returns a timeperiod of num hours, or extracts hours from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('hours', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT HOURS(3);
  --> 10800

SELECT HOURS(1.5);
  --> 5400

SELECT HOURS(timeperiod '3 hours 37 minutes');
  --> 3.62

fn MINUTES

MINUTES(num: float64) -> timeperiod

MINUTES(tp: timeperiod) -> f64

Returns a timeperiod of num minutes, or extracts minutes from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('minutes', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MINUTES(3);
  --> 180

SELECT MINUTES(1.5);
  --> 90

SELECT MINUTES(timeperiod '17 minutes 34 seconds');
  --> 17.57

fn SECONDS

SECONDS(num: float64) -> timeperiod

SECONDS(tp: timeperiod) -> f64

Returns a timeperiod of num seconds, or extracts seconds from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('seconds', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT SECONDS(3);
  --> 3

SELECT SECONDS(1.5);
  --> 1.5

SELECT SECONDS(timeperiod '24s 521ms');
  --> 24.52

fn MILLISECONDS

MILLISECONDS(num: float64) -> timeperiod

MILLISECONDS(tp: timeperiod) -> f64

Returns a timeperiod of num milliseconds, or extracts milliseconds from a timeperiod.

Notes

The second variant is just a shortcut for calling TS_EXTRACT('milliseconds', ts). For more info, see the TS_EXTRACT docs.

Examples

SELECT MILLISECONDS(150);
  --> 0.15

SELECT MILLISECONDS(100.5);
  --> 0.105

SELECT MILLISECONDS(timeperiod '2 seconds');
  --> 2000