# 13.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 -> date`

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

`current_time -> time with time zone`

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

`current_timestamp -> timestamp with time zone`

Returns the current timestamp 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)`.

`from_iso8601_timestamp`(string) → timestamp with time zone

Parses the ISO 8601 formatted `string` into a `timestamp with time zone`.

`from_iso8601_date`(string) → date

Parses the ISO 8601 formatted `string` into a `date`.

`from_unixtime`(unixtime) → timestamp

Returns the UNIX timestamp `unixtime` as a timestamp.

`from_unixtime`(unixtime, string) → timestamp with time zone

Returns the UNIX timestamp `unixtime` as a timestamp with time zone using `string` for the time zone.

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

`localtime -> time`

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

`localtimestamp -> timestamp`

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.

`date_diff`(unit, timestamp1, timestamp2) → bigint

Returns `timestamp2 - timestamp1` expressed in terms of `unit`.

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

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