Date and time functions and operators

These functions and operators operate on date and time data types.

​

Date and time operators

​

Time zone conversion

The AT TIME ZONE operator sets the time zone of a timestamp:

SELECT timestamp '2012-10-31 01:00 UTC';
-- 2012-10-31 01:00:00.000 UTC

SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles';
-- 2012-10-30 18:00:00.000 America/Los_Angeles

​

Date and time functions

​

current_date

current_date

Returns the current date as of the start of the query.

​

current_time

current_time

Returns the current time with time zone as of the start of the query.

​

current_timestamp

current_timestamp

Returns the current timestamp with time zone as of the start of the query, with 3 digits of subsecond precision,

current_timestamp(p)

Returns the current timestamp with time zone as of the start of the query, with p digits of subsecond precision:

SELECT current_timestamp(6);
-- 2020-06-24 08:25:31.759993 America/Los_Angeles

​

current_timezone

current_timezone() -> varchar

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)

​

date

date(x) -> date

This is an alias for CAST(x AS date).

​

last_day_of_month

last_day_of_month(x) -> date

Returns the last day of the month.

​

from_iso8601_timestamp

from_iso8601_timestamp(string) -> timestamp(3) with time zone

Parses the ISO 8601 formatted date string, optionally with time and time zone, into a timestamp(3) with time zone. The time defaults to 00:00:00.000, and the time zone defaults to the session time zone:

SELECT from_iso8601_timestamp('2020-05-11');
-- 2020-05-11 00:00:00.000 America/Vancouver

SELECT from_iso8601_timestamp('2020-05-11T11:15:05');
-- 2020-05-11 11:15:05.000 America/Vancouver

SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00');
-- 2020-05-11 11:15:05.055 +01:00

​

from_iso8601_timestamp_nanos

from_iso8601_timestamp_nanos(string) -> timestamp(9) with time zone

Parses the ISO 8601 formatted date and time string. The time zone defaults to the session time zone:

SELECT from_iso8601_timestamp_nanos('2020-05-11T11:15:05');
-- 2020-05-11 11:15:05.000000000 America/Vancouver

SELECT from_iso8601_timestamp_nanos('2020-05-11T11:15:05.123456789+01:00');
-- 2020-05-11 11:15:05.123456789 +01:00

​

from_iso8601_date

from_iso8601_date(string) -> date

Parses the ISO 8601 formatted date string into a date. The date can be a calendar date, a week date using ISO week numbering, or year and day of year combined:

SELECT from_iso8601_date('2020-05-11');
-- 2020-05-11

SELECT from_iso8601_date('2020-W10');
-- 2020-03-02

SELECT from_iso8601_date('2020-123');
-- 2020-05-02

​

at_timezone

at_timezone(timestamp(p), zone) -> timestamp(p) with time zone

Returns the timestamp specified in timestamp with the time zone converted from the session time zone to the time zone specified in zone with precision p. In the following example, the session time zone is set to America/New_York, which is three hours ahead of America/Los_Angeles:

SELECT current_timezone()
-- America/New_York

SELECT at_timezone(TIMESTAMP '2022-11-01 09:08:07.321', 'America/Los_Angeles')
-- 2022-11-01 06:08:07.321 America/Los_Angeles

​

with_timezone

with_timezone(timestamp(p), zone) -> timestamp(p) with time zone

Returns the timestamp specified in timestamp with the time zone specified in zone with precision p:

SELECT current_timezone()
-- America/New_York

SELECT with_timezone(TIMESTAMP '2022-11-01 09:08:07.321', 'America/Los_Angeles')
-- 2022-11-01 09:08:07.321 America/Los_Angeles

​

from_unixtime

from_unixtime(unixtime) -> timestamp(3) with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone. unixtime is the number of seconds since 1970-01-01 00:00:00 UTC.

from_unixtime(unixtime, zone) -> timestamp(3) with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone using zone for the time zone. unixtime is the number of seconds since 1970-01-01 00:00:00 UTC.

from_unixtime(unixtime, hours, minutes) -> timestamp(3) with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset. unixtime is the number of seconds since 1970-01-01 00:00:00 in double data type.

​

from_unixtime_nanos

from_unixtime_nanos(unixtime) -> timestamp(9) with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone. unixtime is the number of nanoseconds since 1970-01-01 00:00:00.000000000 UTC:

SELECT from_unixtime_nanos(100);
-- 1970-01-01 00:00:00.000000100 UTC

SELECT from_unixtime_nanos(DECIMAL '1234');
-- 1970-01-01 00:00:00.000001234 UTC

SELECT from_unixtime_nanos(DECIMAL '1234.499');
-- 1970-01-01 00:00:00.000001234 UTC

SELECT from_unixtime_nanos(DECIMAL '-1234');
-- 1969-12-31 23:59:59.999998766 UTC

​

localtime

localtime

Returns the current time as of the start of the query.

​

localtimestamp

localtimestamp

Returns the current timestamp as of the start of the query, with 3 digits of subsecond precision.

localtimestamp(p)

Returns the current timestamp as of the start of the query, with p digits of subsecond precision:

SELECT localtimestamp(6);
-- 2020-06-10 15:55:23.383628

​

now

now() -> timestamp(3) with time zone

This is an alias for current_timestamp.

​

to_iso8601

to_iso8601(x) -> varchar

Formats x as an ISO 8601 string. x can be date, timestamp, or timestamp with time zone.

​

to_milliseconds

to_milliseconds(interval) -> bigint

Returns the day-to-second interval as milliseconds.

​

to_unixtime

to_unixtime(timestamp) -> double

Returns timestamp as a UNIX timestamp.

​

Truncation function

The date_trunc function supports the following units:

The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.

​

date_trunc

date_trunc(unit, x) -> [same as input]

Returns x truncated to unit:

SELECT date_trunc('day' , TIMESTAMP '2022-10-20 05:10:00');
-- 2022-10-20 00:00:00.000

SELECT date_trunc('month' , TIMESTAMP '2022-10-20 05:10:00');
-- 2022-10-01 00:00:00.000

SELECT date_trunc('year', TIMESTAMP '2022-10-20 05:10:00');
-- 2022-01-01 00:00:00.000

​

Interval functions

The functions in this section support the following interval units:

​

date_add

date_add(unit, value, timestamp) -> [same as input]

Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value:

SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00');
-- 2020-03-01 00:01:26.000

SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00');
-- 2020-03-01 09:00:00.000

SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC');
-- 2020-02-29 00:00:00.000 UTC

​

date_diff

date_diff(unit, timestamp1, timestamp2) -> bigint

Returns timestamp2 - timestamp1 expressed in terms of unit:

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');
-- 86400

SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');
-- 24

SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');
-- 1

SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400

SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400123

​

Duration function

The parse_duration function supports the following units:

​

parse_duration

parse_duration(string) -> interval

Parses string of format value unit into an interval, where value is fractional number of unit values:

SELECT parse_duration('42.8ms');
-- 0 00:00:00.043

SELECT parse_duration('3.81 d');
-- 3 19:26:24.000

SELECT parse_duration('5m');
-- 0 00:05:00.000

​

human_readable_seconds

human_readable_seconds(double) -> varchar

Formats the double value of seconds into a human readable string containing weeks, days, hours, minutes, and seconds:

SELECT human_readable_seconds(96);
-- 1 minute, 36 seconds

SELECT human_readable_seconds(3762);
-- 1 hour, 2 minutes, 42 seconds

SELECT human_readable_seconds(56363463);
-- 93 weeks, 1 day, 8 hours, 31 minutes, 3 seconds

​

MySQL date functions

The functions in this section use a format string that is compatible with the MySQL date_parse and str_to_date functions. The following table, based on the MySQL manual, describes the format specifiers:

​

date_format

date_format(timestamp, format) -> varchar

Formats timestamp as a string using format:

SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H');
-- 10-20-2022 05

​

date_parse

date_parse(string, format) → timestamp(3)

Parses string into a timestamp using format:

SELECT date_parse('2022/10/20/05', '%Y/%m/%d/%H');
-- 2022-10-20 05:00:00.000

​

Java date functions

The functions in this section use a format string that is compatible with JodaTime’s DateTimeFormat pattern format.

​

format_datetime

format_datetime(timestamp, format) -> varchar

Formats timestamp as a string using format.

​

parse_datetime

parse_datetime(string, format) -> timestamp with time zone

Parses string into a timestamp with time zone using format.

​

Extraction function

The extract function supports the following fields:

The types supported by the extract function vary depending on the field to be extracted. Most fields support all date and time types.

​

extract

extract(field FROM x) -> bigint

Returns field from x:

SELECT extract(YEAR FROM TIMESTAMP '2022-10-20 05:10:00');
-- 2022

​

Convenience extraction functions

​

day

day(x) -> bigint

Returns the day of the month from x.

​

day_of_month

day_of_month(x) -> bigint

This is an alias for day.

​

day_of_week

day_of_week(x) -> bigint

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday).

​

day_of_year

day_of_year(x) -> bigint

Returns the day of the year from x. The value ranges from 1 to 366.

​

dow

dow(x) -> bigint

This is an alias for day_of_week.

​

doy

doy(x) -> bigint

This is an alias for day_of_year.

​

hour

hour(x) -> bigint

Returns the hour of the day from x. The value ranges from 0 to 23.

​

millisecond

millisecond(x) -> bigint

Returns the millisecond of the second from x.

​

minute

minute(x) -> bigint

Returns the minute of the hour from x.

​

month

month(x) -> bigint

Returns the month of the year from x.

​

quarter

quarter(x) -> bigint

Returns the quarter of the year from x. The value ranges from 1 to 4.

​

second

second(x) -> bigint

Returns the second of the minute from x.

​

timezone_hour

timezone_hour(timestamp) -> bigint

Returns the hour of the time zone offset from timestamp.

​

timezone_minute

timezone_minute(timestamp) -> bigint

Returns the minute of the time zone offset from timestamp.

​

week

week(x) -> bigint

Returns the ISO week of the year from x. The value ranges from 1 to 53.

​

week_of_year

week_of_year(x) -> bigint

This is an alias for week.

​

year

year(x) -> bigint

Returns the year from x.

​

year_of_week

year_of_week(x) -> bigint

Returns the year of the [ISO week] from x.

​

yow

yow(x) -> bigint

This is an alias for year_of_week.