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

export const QueryWindow = ({content}) => {
  const {sql, result} = content;
  const [inited, setInited] = useState(false);
  const buttonRef = useRef(null);
  useEffect(() => {
    if (!inited && buttonRef.current) {
      runQuery(buttonRef.current, true);
      setInited(true);
    }
  }, []);
  return <div className="query-window">
      <div className="query-toolbar">
        <button className="run-button" onClick={ev => runQuery(ev.target)} ref={buttonRef}>Run Query</button>
        <span className="window-title">Interactive SQL Playground 🔥</span>
      </div>
      <div className="query-content">
        <pre><code className="firebolt-sql language-sql" contentEditable="true" spellCheck="false" data-original-query={sql}>{sql}</code></pre>
        <script type="application/json" className="fallback-result" style={{
    display: "none"
  }}>{JSON.stringify(result)}</script>
        <div className="server-unavailable-banner query-window-hidden">
          The Firebolt playground server is currently unavailable. Using precomputed query results.
        </div>
        <div className="query-results"></div>
      </div>
    </div>;
};

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

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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":"github-light","dark":"github-dark"}}
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.

<QueryWindow
  content={{
"sql": "SELECT DATE_TRUNC('century', DATE '1996-09-03');",
"result": {
"data": [
  [
    "1901-01-01"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "date"
  }
],
"query": {
  "query_id": "b778e20e-5e31-4d71-80d5-b39e067d3873",
  "query_label": null,
  "request_id": "1e53adcb-9024-4294-8005-1f3b5d168add"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.00648,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000245724,
  "time_to_execute": 9.0714e-05
}
}
}}
/>

<QueryWindow
  content={{
"sql": "SELECT DATE_TRUNC('hour', TIMESTAMP '1996-09-03 11:19:42.123');",
"result": {
"data": [
  [
    "1996-09-03 11:00:00"
  ]
],
"meta": [
  {
    "name": "?column?",
    "type": "timestamp"
  }
],
"query": {
  "query_id": "0384cc82-bc97-4d2d-8196-daec48499e9f",
  "query_label": null,
  "request_id": "ac8f6ad2-596f-4e70-8840-d371081bee52"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006359,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000249389,
  "time_to_execute": 9.8152e-05
}
}
}}
/>

You can also truncate `TIMESTAMPTZ` values.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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');
```