> ## Documentation Index
> Fetch the complete documentation index at: https://docs.peaka.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Date and time functions and operators
These functions and operators operate on [date and time data types](/sql/reference/types#date-time-data-types).
## 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:
```sql theme={null}
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](/sql/reference/types#timestamp-with-time-zone-data-type) as of the start of the query, with
`p` digits of subsecond precision:
```sql theme={null}
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:
```sql theme={null}
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:
```sql theme={null}
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:
```sql theme={null}
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`:
```sql theme={null}
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`:
```sql theme={null}
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`:
```sql theme={null}
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](/sql/reference/types#timestamp) as of the start
of the query, with `p` digits of subsecond precision:
```sql theme={null}
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.
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 |
| ------------- | ------------------------- |
| `millisecond` | `2001-08-22 03:04:05.321` |
| `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
```
date_trunc(unit, x) -> [same as input]
```
Returns `x` truncated to `unit`:
```sql theme={null}
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 |
| ------------- | ------------------ |
| `millisecond` | Milliseconds |
| `second` | Seconds |
| `minute` | Minutes |
| `hour` | Hours |
| `day` | Days |
| `week` | Weeks |
| `month` | Months |
| `quarter` | Quarters of a year |
| `year` | Years |
### 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:
```sql theme={null}
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`:
```sql theme={null}
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 |
| ---- | ------------ |
| `ns` | Nanoseconds |
| `us` | Microseconds |
| `ms` | Milliseconds |
| `s` | Seconds |
| `m` | Minutes |
| `h` | Hours |
| `d` | Days |
### parse\_duration
```
parse_duration(string) -> interval
```
Parses `string` of format `value unit` into an interval, where
`value` is fractional number of `unit` values:
```sql theme={null}
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`:
```sql theme={null}
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 |
| --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `%a` | Abbreviated weekday name (`Sun` .. `Sat`) |
| `%b` | Abbreviated month name (`Jan` .. `Dec`) |
| `%c` | Month, numeric (`1` .. `12`), this specifier does not support `0` as a month. |
| `%D` | Day of the month with English suffix (`0th`, `1st`, `2nd`, `3rd`, ...) |
| `%d` | Day of the month, numeric (`01` .. `31`), this specifier does not support `0` as a month or day. |
| `%e` | Day of the month, numeric (`1` .. `31`), this specifier does not support `0` as a day. |
| `%f` | Fraction of second (6 digits for printing: `000000` .. `999000`; 1 - 9 digits for parsing: `0` .. `999999999`), timestamp is truncated to milliseconds. |
| `%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`), this specifier does not support `0` as a month. |
| `%p` | `AM` or `PM` |
| `%r` | Time of day, 12-hour (equivalent to `%h:%i:%s %p`) |
| `%S` | Seconds (`00` .. `59`) |
| `%s` | Seconds (`00` .. `59`) |
| `%T` | Time of day, 24-hour (equivalent to `%H:%i:%s`) |
| `%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, this specifier is not supported,consider using `day_of_week` (it uses `1-7` instead of `0-6`). |
| `%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), when parsing, two-digit year format assumes range `1970` .. `2069`, so "70" will result in year `1970` but "69" will produce `2069`. |
| `%%` | A literal `%` character |
| `%x` | `x`, for any `x` not listed above |
The following specifiers are not currently supported: `%D %U %u %V %w %X`
### date\_format
```
date_format(timestamp, format) -> varchar
```
Formats `timestamp` as a string using `format`:
```sql theme={null}
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`:
```sql theme={null}
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:
| 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
```
extract(field FROM x) -> bigint
```
Returns `field` from `x`:
```sql theme={null}
SELECT extract(YEAR FROM TIMESTAMP '2022-10-20 05:10:00');
-- 2022
```
This SQL-standard function uses special syntax for specifying the arguments.
## 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](#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](#year_of_week).
[datetimeformat]: http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html