> ## 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](/reference-sql/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 | |
| [`LAST_DAY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#last_day) | Supported | Optional date part: `WEEK`, `ISOWEEK`, `MONTH` (default), `QUARTER`, or `YEAR`. `WEEK(<weekday>)` is approximated to a Monday-based week. |

## 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) | Supported | Optional date part: `WEEK`, `ISOWEEK`, `MONTH` (default), `QUARTER`, or `YEAR`. `WEEK(<weekday>)` is approximated to a Monday-based week. |

## 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](/reference-sql/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.


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