7.13. Date and Time Functions and Operators#

Date and Time Operators#

Operator	Example	Result
`+`	`date '2012-08-08' + interval '2' day`	`2012-08-10`
`+`	`time '01:00' + interval '3' hour`	`04:00:00.000`
`+`	`timestamp '2012-08-08 01:00' + interval '29' hour`	`2012-08-09 06:00:00.000`
`+`	`timestamp '2012-10-31 01:00' + interval '1' month`	`2012-11-30 01:00:00.000`
`+`	`interval '2' day + interval '3' hour`	`2 03:00:00.000`
`+`	`interval '3' year + interval '5' month`	`3-5`
`-`	`date '2012-08-08' - interval '2' day`	`2012-08-06`
`-`	`time '01:00' - interval '3' hour`	`22:00:00.000`
`-`	`timestamp '2012-08-08 01:00' - interval '29' hour`	`2012-08-06 20:00:00.000`
`-`	`timestamp '2012-10-31 01:00' - interval '1' month`	`2012-09-30 01:00:00.000`
`-`	`interval '2' day - interval '3' hour`	`1 21:00:00.000`
`-`	`interval '3' year - interval '5' month`	`2-7`

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.

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 with time zone#

Parses the ISO 8601 formatted date string, optionally with time and time zone, into a timestamp with time zone. The time defaults to zero, 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_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(timestamp, zone) → timestamp with time zone#: Change the time zone component of timestamp to zone while preserving the instant in time.

with_timezone(timestamp, zone) → timestamp with time zone#: Returns a timestamp with time zone from timestamp and zone.

from_unixtime(unixtime) → timestamp#: Returns the UNIX timestamp unixtime as a timestamp. unixtime is the number of seconds since 1970-01-01 00:00:00.

from_unixtime(unixtime, zone) → timestamp 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.

from_unixtime(unixtime, hours, minutes) → timestamp 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.

localtime#: Returns the current time as of the start of the query.

localtimestamp#: Returns the current timestamp as of the start of the query.

now() → timestamp with time zone#: This is an alias for current_timestamp.

to_iso8601(x) → varchar#: Formats x as an ISO 8601 string. x can be date, timestamp, or timestamp with time zone.

to_milliseconds(interval) → bigint#: Returns the day-to-second interval as milliseconds.

to_unixtime(timestamp) → double#: Returns timestamp as 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
`second`	`2001-08-22 03:04:05.000`
`minute`	`2001-08-22 03:04:00.000`
`hour`	`2001-08-22 03:00:00.000`
`day`	`2001-08-22 00:00:00.000`
`week`	`2001-08-20 00:00:00.000`
`month`	`2001-08-01 00:00:00.000`
`quarter`	`2001-07-01 00:00:00.000`
`year`	`2001-01-01 00:00:00.000`

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

date_trunc(unit, x) → [same as input]#: Returns x truncated to unit.

Interval Functions#

The functions in this section support the following interval units:

Unit	Description
`millisecond`	Milliseconds
`second`	Seconds
`minute`	Minutes
`hour`	Hours
`day`	Days
`week`	Weeks
`month`	Months
`quarter`	Quarters of a year
`year`	Years

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(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

Duration Function#

The parse_duration function supports the following units:

Unit	Description
`ns`	Nanoseconds
`us`	Microseconds
`ms`	Milliseconds
`s`	Seconds
`m`	Minutes
`h`	Hours
`d`	Days

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

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
`%a`	Abbreviated weekday name (`Sun` .. `Sat`)
`%b`	Abbreviated month name (`Jan` .. `Dec`)
`%c`	Month, numeric (`1` .. `12`) [4]
`%D`	Day of the month with English suffix (`0th`, `1st`, `2nd`, `3rd`, …)
`%d`	Day of the month, numeric (`01` .. `31`) [4]
`%e`	Day of the month, numeric (`1` .. `31`) [4]
`%f`	Fraction of second (6 digits for printing: `000000` .. `999000`; 1 - 9 digits for parsing: `0` .. `999999999`) [1]
`%H`	Hour (`00` .. `23`)
`%h`	Hour (`01` .. `12`)
`%I`	Hour (`01` .. `12`)
`%i`	Minutes, numeric (`00` .. `59`)
`%j`	Day of year (`001` .. `366`)
`%k`	Hour (`0` .. `23`)
`%l`	Hour (`1` .. `12`)
`%M`	Month name (`January` .. `December`)
`%m`	Month, numeric (`01` .. `12`) [4]
`%p`	`AM` or `PM`
`%r`	Time, 12-hour (`hh:mm:ss` followed by `AM` or `PM`)
`%S`	Seconds (`00` .. `59`)
`%s`	Seconds (`00` .. `59`)
`%T`	Time, 24-hour (`hh:mm:ss`)
`%U`	Week (`00` .. `53`), where Sunday is the first day of the week
`%u`	Week (`00` .. `53`), where Monday is the first day of the week
`%V`	Week (`01` .. `53`), where Sunday is the first day of the week; used with `%X`
`%v`	Week (`01` .. `53`), where Monday is the first day of the week; used with `%x`
`%W`	Weekday name (`Sunday` .. `Saturday`)
`%w`	Day of the week (`0` .. `6`), where Sunday is the first day of the week [3]
`%X`	Year for the week where Sunday is the first day of the week, numeric, four digits; used with `%V`
`%x`	Year for the week, where Monday is the first day of the week, numeric, four digits; used with `%v`
`%Y`	Year, numeric, four digits
`%y`	Year, numeric (two digits) [2]
`%%`	A literal `%` character
`%x`	`x`, for any `x` not listed above

[1]	Timestamp is truncated to milliseconds.

[2]	When parsing, two-digit year format assumes range `1970` .. `2069`, so “70” will result in year `1970` but “69” will produce `2069`.

[3]	This specifier is not supported yet. Consider using `day_of_week()` (it uses `1-7` instead of `0-6`).

[4]	(1, 2, 3, 4) This specifier does not support `0` as a month or day.

Warning

The following specifiers are not currently supported: %D %U %u %V %w %X

date_format(timestamp, format) → varchar#: Formats timestamp as a string using format.

date_parse(string, format) → timestamp#: Parses string into a timestamp using format.

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 timestamp as a string using format.

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:

Field	Description
`YEAR`	`year()`
`QUARTER`	`quarter()`
`MONTH`	`month()`
`WEEK`	`week()`
`DAY`	`day()`
`DAY_OF_MONTH`	`day()`
`DAY_OF_WEEK`	`day_of_week()`
`DOW`	`day_of_week()`
`DAY_OF_YEAR`	`day_of_year()`
`DOY`	`day_of_year()`
`YEAR_OF_WEEK`	`year_of_week()`
`YOW`	`year_of_week()`
`HOUR`	`hour()`
`MINUTE`	`minute()`
`SECOND`	`second()`
`TIMEZONE_HOUR`	`timezone_hour()`
`TIMEZONE_MINUTE`	`timezone_minute()`

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 field from x.

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_month(x) → bigint#: This is an alias for day().

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 1 to 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 0 to 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 1 to 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.

week(x) → bigint#: Returns the ISO week of the year from x. The value ranges from 1 to 53.

week_of_year(x) → bigint#: This is an alias for week().

year(x) → bigint#: Returns the year from x.

year_of_week(x) → bigint#: Returns the year of the ISO week from x.

yow(x) → bigint#: This is an alias for year_of_week().

Presto 334 Documentation