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

> Reference material for the DATE_TRUNC function

# DATE_TRUNC

Truncates a value (`<expression>`) of type `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` to the selected precision (`<time_unit>`).

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
DATE_TRUNC(<time_unit>, <expression> [, <time_zone> ])
```

## Parameters

| Parameter      | Description                                                                                                                                                                                                          |
| :------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<time_unit>`  | A TEXT literal with the time unit precision to truncate to. Must be one of `microsecond`, `millisecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`, `decade`, `century` or `millennium`. |
| `<expression>` | A value expression evaluating to the DATE, TIMESTAMP, or TIMESTAMPTZ value that should be truncated.                                                                                                                 |
| `<time_zone>`  | An optional TEXT literal giving a time zone name.                                                                                                                                                                    |

## Return Type

DATE if `<expression>` has type DATE, TIMESTAMP if `<expression>` has type TIMESTAMP, TIMESTAMPTZ if `<expression>` has type TIMESTAMPTZ.

## Remarks

Truncation of TIMESTAMPTZ values is performed after conversion to local time in a particular time zone.
For instance, truncation to 'day' produces a TIMESTAMPTZ that is midnight in that time zone.
By default, the function uses the time zone specified in the session's `time_zone` setting.
Alternatively, if the optional `<time_zone>` argument is provided, the function uses that time zone.

Firebolt raises an error if the optional `<time_zone>` argument is provided for an `<expression>` evaluating to DATE or TIMESTAMP.
Firebolt also raises an error if one attempts to truncate a value expression of type DATE to `microsecond`, `millisecond`, `second`, `minute`, or `hour`.

The `DATE_TRUNC` function can be used in the `PARTITION BY` clause of `CREATE TABLE` commands.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE DIMENSION TABLE test (
  d DATE,
  t TIMESTAMP
)
PARTITION BY DATE_TRUNC('month', d), DATE_TRUNC('hour', t);
```

## Example

The following examples truncate `DATE` and `TIMESTAMP` values without timezones.

<div className="query-window">
  ```
  SELECT DATE_TRUNC('century', DATE '1996-09-03');
  ```

  | date\_trunc <span>date</span> |
  | :---------------------------- |
  | 1901-01-01                    |

  <p><span>Rows: 1</span><span>Execution time: 5.54ms</span></p>
</div>

<div className="query-window">
  ```
  SELECT DATE_TRUNC('hour', TIMESTAMP '1996-09-03 11:19:42.123');
  ```

  | date\_trunc <span>timestamp</span> |
  | :--------------------------------- |
  | 1996-09-03 11:00:00                |

  <p><span>Rows: 1</span><span>Execution time: 5.62ms</span></p>
</div>

You can also truncate `TIMESTAMPTZ` values.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SET time_zone = 'US/Pacific';
-- Returns 1996-09-02 00:00:00-07
SELECT DATE_TRUNC('week', TIMESTAMPTZ '1996-09-03 11:19:42.123 Europe/Berlin');  
-- Returns 1996-09-01 15:00:00-07
SELECT DATE_TRUNC('week', TIMESTAMPTZ '1996-09-03 11:19:42.123 Europe/Berlin', 'Europe/Berlin');
```