> ## Documentation Index
> Fetch the complete documentation index at: https://docs.upsolve.ai/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.

<Note>
  The following SQL-standard functions do not use parenthesis:

  * `current_date`
  * `current_time`
  * `current_timestamp`
  * `localtime`
  * `localtimestamp`
</Note>

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

<Warning>
  The following specifiers are not currently supported: `%D %U %u %V %w %X`
</Warning>

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

<Note>
  This SQL-standard function uses special syntax for specifying the arguments.
</Note>

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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.