Date and time functions and operators#
These functions and operators operate on date and time data types.
Date and time operators#
| Operator | Example | Result | 
|---|---|---|
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
| 
 | 
 | 
 | 
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#
- Returns the current date as of the start of the query. 
- current_time#
- Returns the current time with time zone as of the start of the query. 
- current_timestamp#
- Returns the current timestamp with time zone as of the start of the query, with - 3digits of subsecond precision,
- current_timestamp(p)
- Returns the current timestamp with time zone as of the start of the query, with - pdigits of subsecond precision:- SELECT current_timestamp(6); -- 2020-06-24 08:25:31.759993 America/Los_Angeles 
- 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(x) date#
- This is an alias for - CAST(x AS date).
- last_day_of_month(x) date#
- Returns the last day of the month. 
- 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(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(string) date#
- Parses the ISO 8601 formatted date - stringinto 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(timestamp(p), zone) timestamp(p) with time zone#
- Returns the timestamp specified in - timestampwith the time zone converted from the session time zone to the time zone specified in- zonewith 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(timestamp(p), zone) timestamp(p) with time zone#
- Returns the timestamp specified in - timestampwith the time zone specified in- zonewith 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(unixtime) timestamp(3) with time zone#
- Returns the UNIX timestamp - unixtimeas a timestamp with time zone.- unixtimeis 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 - unixtimeas a timestamp with time zone using- zonefor the time zone.- unixtimeis 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 - unixtimeas a timestamp with time zone using- hoursand- minutesfor the time zone offset.- unixtimeis the number of seconds since- 1970-01-01 00:00:00in- doubledata type.
- from_unixtime_nanos(unixtime) timestamp(9) with time zone#
- Returns the UNIX timestamp - unixtimeas a timestamp with time zone.- unixtimeis 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#
- Returns the current time as of the start of the query. 
- localtimestamp#
- Returns the current timestamp as of the start of the query, with - 3digits of subsecond precision.
- localtimestamp(p)
- Returns the current timestamp as of the start of the query, with - pdigits of subsecond precision:- SELECT localtimestamp(6); -- 2020-06-10 15:55:23.383628 
- now() timestamp(3) with time zone#
- This is an alias for - current_timestamp.
- to_iso8601(x) varchar#
- Formats - xas an ISO 8601 string.- xcan be date, timestamp, or timestamp with time zone.
- to_milliseconds(interval) bigint#
- Returns the day-to-second - intervalas milliseconds.
- to_unixtime(timestamp) double#
- Returns - timestampas a UNIX timestamp.
Note
The following SQL-standard functions do not use parenthesis:
- current_date
- current_time
- current_timestamp
- localtime
- localtimestamp
Truncation function#
The date_trunc function supports the following units:
| Unit | Example Truncated Value | 
|---|---|
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
| 
 | 
 | 
The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.
- date_trunc(unit, x) [same as input]#
- Returns - xtruncated 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:
| Unit | Description | 
|---|---|
| 
 | Milliseconds | 
| 
 | Seconds | 
| 
 | Minutes | 
| 
 | Hours | 
| 
 | Days | 
| 
 | Weeks | 
| 
 | Months | 
| 
 | Quarters of a year | 
| 
 | Years | 
- date_add(unit, value, timestamp) [same as input]#
- Adds an interval - valueof type- unitto- 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(unit, timestamp1, timestamp2) bigint#
- Returns - timestamp2 - timestamp1expressed 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:
| Unit | Description | 
|---|---|
| 
 | Nanoseconds | 
| 
 | Microseconds | 
| 
 | Milliseconds | 
| 
 | Seconds | 
| 
 | Minutes | 
| 
 | Hours | 
| 
 | Days | 
- parse_duration(string) interval#
- Parses - stringof format- value unitinto an interval, where- valueis fractional number of- unitvalues:- 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(double) varchar#
- Formats the double value of - secondsinto 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:
| Specifier | Description | 
|---|---|
| 
 | Abbreviated weekday name ( | 
| 
 | Abbreviated month name ( | 
| 
 | Month, numeric ( | 
| 
 | Day of the month with English suffix ( | 
| 
 | Day of the month, numeric ( | 
| 
 | Day of the month, numeric ( | 
| 
 | Fraction of second (6 digits for printing:  | 
| 
 | Hour ( | 
| 
 | Hour ( | 
| 
 | Hour ( | 
| 
 | Minutes, numeric ( | 
| 
 | Day of year ( | 
| 
 | Hour ( | 
| 
 | Hour ( | 
| 
 | Month name ( | 
| 
 | Month, numeric ( | 
| 
 | 
 | 
| 
 | Time of day, 12-hour (equivalent to  | 
| 
 | Seconds ( | 
| 
 | Seconds ( | 
| 
 | Time of day, 24-hour (equivalent to  | 
| 
 | Week ( | 
| 
 | Week ( | 
| 
 | Week ( | 
| 
 | Week ( | 
| 
 | Weekday name ( | 
| 
 | Day of the week ( | 
| 
 | Year for the week where Sunday is the first day of the week, numeric, four digits; used with  | 
| 
 | Year for the week, where Monday is the first day of the week, numeric, four digits; used with  | 
| 
 | Year, numeric, four digits | 
| 
 | Year, numeric (two digits), when parsing, two-digit year format assumes range  | 
| 
 | A literal  | 
| 
 | 
 | 
Warning
The following specifiers are not currently supported: %D %U %u %V %w %X
- date_format(timestamp, format) varchar#
- Formats - timestampas a string using- format:- SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H'); -- 10-20-2022 05 
- date_parse(string, format) → timestamp(3)#
- Parses - stringinto 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(timestamp, format) varchar#
- Formats - timestampas a string using- format.
- parse_datetime(string, format) timestamp with time zone#
- Parses - stringinto a timestamp with time zone using- format.
Extraction function#
The extract function supports the following fields:
| Field | Description | 
|---|---|
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | |
| 
 | 
The types supported by the extract function vary depending on the
field to be extracted. Most fields support all date and time types.
- extract(field FROM x) bigint#
- Returns - fieldfrom- x:- SELECT extract(YEAR FROM TIMESTAMP '2022-10-20 05:10:00'); -- 2022 - Note - This SQL-standard function uses special syntax for specifying the arguments. 
Convenience extraction functions#
- day(x) bigint#
- Returns the day of the month from - x.
- 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(x) bigint#
- Returns the day of the year from - x. The value ranges from- 1to- 366.
- dow(x) bigint#
- This is an alias for - day_of_week().
- doy(x) bigint#
- This is an alias for - day_of_year().
- hour(x) bigint#
- Returns the hour of the day from - x. The value ranges from- 0to- 23.
- millisecond(x) bigint#
- Returns the millisecond of the second from - x.
- minute(x) bigint#
- Returns the minute of the hour from - x.
- month(x) bigint#
- Returns the month of the year from - x.
- quarter(x) bigint#
- Returns the quarter of the year from - x. The value ranges from- 1to- 4.
- second(x) bigint#
- Returns the second of the minute from - x.
- timezone_hour(timestamp) bigint#
- Returns the hour of the time zone offset from - timestamp.
- timezone_minute(timestamp) bigint#
- Returns the minute of the time zone offset from - timestamp.
- year(x) bigint#
- Returns the year from - x.
- yow(x) bigint#
- This is an alias for - year_of_week().