> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Date and time functions

> BigQuery date, datetime, time, timestamp, and interval functions supported in BigQuery compatibility mode.

<span className="feature-tag">Preview</span>

BigQuery date and time functions in [BigQuery compatibility mode](/compatibility-modes/bigquery/overview), by BigQuery category. Support is one of: Supported, With limitations, or Not supported.

## Date functions

BigQuery reference: [Date functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions).

| Function                                                                                                                       | Support          | Notes                                                     |
| :----------------------------------------------------------------------------------------------------------------------------- | :--------------- | :-------------------------------------------------------- |
| [`CURRENT_DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#current_date)               | Supported        | Optional timezone argument.                               |
| [`DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date)                               | Supported        | Constructor and extraction forms.                         |
| [`DATE_ADD`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_add)                       | Supported        |                                                           |
| [`DATE_SUB`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_sub)                       | Supported        |                                                           |
| [`DATE_DIFF`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_diff)                     | Supported        |                                                           |
| [`DATE_TRUNC`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_trunc)                   | With limitations | `WEEK(<weekday>)` is approximated to a Monday-based week. |
| [`DATE_FROM_UNIX_DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_from_unix_date) | Supported        |                                                           |
| [`UNIX_DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date)                     | Supported        |                                                           |
| [`FORMAT_DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#format_date)                 | Supported        | Format string must be a literal.                          |
| [`PARSE_DATE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date)                   | Supported        | Format string must be a literal.                          |
| [`EXTRACT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract)                         | Supported        | An explicit week-start weekday is not supported.          |
| [`LAST_DAY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#last_day)                       | Not supported    |                                                           |

## Datetime functions

BigQuery reference: [Datetime functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions).

| Function                                                                                                                     | Support          | Notes                                                     |
| :--------------------------------------------------------------------------------------------------------------------------- | :--------------- | :-------------------------------------------------------- |
| [`CURRENT_DATETIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#current_datetime) | Supported        | Optional timezone argument.                               |
| [`DATETIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime)                 | Supported        | Constructor forms, including six-part and `(date, time)`. |
| [`DATETIME_ADD`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_add)         | Supported        |                                                           |
| [`DATETIME_SUB`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_sub)         | Supported        |                                                           |
| [`DATETIME_DIFF`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_diff)       | Supported        |                                                           |
| [`DATETIME_TRUNC`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_trunc)     | With limitations | `WEEK(<weekday>)` is approximated to a Monday-based week. |
| [`FORMAT_DATETIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#format_datetime)   | Supported        | Format string must be a literal.                          |
| [`PARSE_DATETIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#parse_datetime)     | Supported        | Format string must be a literal.                          |
| [`EXTRACT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#extract)                   | Supported        |                                                           |
| [`LAST_DAY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#last_day)                 | Not supported    |                                                           |

## Timestamp functions

BigQuery reference: [Timestamp functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions).

| Function                                                                                                                        | Support          | Notes                                                                                               |
| :------------------------------------------------------------------------------------------------------------------------------ | :--------------- | :-------------------------------------------------------------------------------------------------- |
| [`CURRENT_TIMESTAMP`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp) | Supported        |                                                                                                     |
| [`TIMESTAMP`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp)                 | Supported        | Constructor with optional timezone.                                                                 |
| [`TIMESTAMP_ADD`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_add)         | Supported        |                                                                                                     |
| [`TIMESTAMP_SUB`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_sub)         | Supported        |                                                                                                     |
| [`TIMESTAMP_DIFF`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_diff)       | Supported        |                                                                                                     |
| [`TIMESTAMP_TRUNC`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_trunc)     | Supported        | Optional timezone argument.                                                                         |
| [`TIMESTAMP_SECONDS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_seconds) | Supported        |                                                                                                     |
| [`TIMESTAMP_MILLIS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_millis)   | Supported        |                                                                                                     |
| [`TIMESTAMP_MICROS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_micros)   | Supported        |                                                                                                     |
| [`UNIX_SECONDS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#unix_seconds)           | Supported        |                                                                                                     |
| [`UNIX_MILLIS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#unix_millis)             | Supported        |                                                                                                     |
| [`UNIX_MICROS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#unix_micros)             | Supported        |                                                                                                     |
| [`FORMAT_TIMESTAMP`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp)   | Supported        | Optional timezone argument. Format string must be a literal.                                        |
| [`PARSE_TIMESTAMP`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#parse_timestamp)     | Supported        | Optional timezone argument. Format string must be a literal.                                        |
| [`STRING`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#string)                       | With limitations | One-argument cast to text is supported. The two-argument `STRING(timestamp, timezone)` form is not. |
| [`EXTRACT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract)                     | Supported        |                                                                                                     |

## Time functions

BigQuery reference: [Time functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions). BigQuery `TIME` has no native Firebolt type and is emulated on a timezone-naive timestamp, so most time-specific functions are not available.

| Function                                                                                                                                                                                                                                                                                                                                                                                                                                     | Support          | Notes                                                   |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :--------------- | :------------------------------------------------------ |
| [`TIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time)                                                                                                                                                                                                                                                                                                                                             | With limitations | Constructor only. The value carries a placeholder date. |
| [`CURRENT_TIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#current_time)                                                                                                                                                                                                                                                                                                                             | Not supported    |                                                         |
| [`TIME_ADD`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time_add), [`TIME_SUB`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time_sub), [`TIME_DIFF`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time_diff), [`TIME_TRUNC`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time_trunc) | Not supported    |                                                         |
| [`FORMAT_TIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#format_time), [`PARSE_TIME`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#parse_time)                                                                                                                                                                                                                 | Not supported    |                                                         |

## Interval functions

BigQuery reference: [Interval functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions). An `INTERVAL` is usable only as a constant or expression operand; see [Data types](/compatibility-modes/bigquery/data-types).

| Function                                                                                                                                                                                                                                                                                                                                                                   | Support       | Notes                            |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------ | :------------------------------- |
| [`EXTRACT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#extract)                                                                                                                                                                                                                                                                 | Supported     | Extract a part from an interval. |
| [`MAKE_INTERVAL`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#make_interval)                                                                                                                                                                                                                                                     | Not supported | Use an `INTERVAL` literal.       |
| [`JUSTIFY_DAYS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#justify_days), [`JUSTIFY_HOURS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#justify_hours), [`JUSTIFY_INTERVAL`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#justify_interval) | Not supported |                                  |

## Format elements

`FORMAT_*` and `PARSE_*` accept these BigQuery format elements: `%Y %y %m %d %e %j %H %I %M %S %p %b %B %a %A %Q %V %W %U %X %F %T %%`, plus the `%-` no-padding flag.

* `%W` and `%U` (week of year) are translated to the ISO week, which numbers weeks slightly differently from BigQuery near year boundaries.
* `%X` expands to `HH24:MI:SS`.

## Truncation and diff parts

`*_TRUNC` and `*_DIFF` accept: `MICROSECOND`, `MILLISECOND`, `SECOND`, `MINUTE`, `HOUR`, `DAY`, `WEEK`, `ISOWEEK`, `MONTH`, `QUARTER`, `YEAR`, `DECADE`, `CENTURY`, and `MILLENNIUM`. `*_BUCKET` accepts a width of `INTERVAL 1 unit` only.