# DATE\_TRUNC

Truncates a timestamp using the specified date part.

Returns a truncated timestamp where the date part is used as the level of precision.

## Syntax

```
DATE_TRUNC('datepart', timestamp)

```

## Arguments

* `'datepart'`: The date part used to truncate the `timestamp`, which can be one of the following levels of precision:

  * `year`
  * `quarter`
  * `month`
  * `week`
  * `day`
  * `hour`
  * `minute`
  * `second`
  * `microseconds`
* `timestamp`: The timestamp to truncate, either as a column of `TIMESTAMP` type or an expression that evaluates to `TIMESTAMP`.

> **📝 Note**: When `datepart` is set to `week`, the `timestamp` specified is truncated to start on the first day of the week, which is Monday in SingleStore.

## Return Type

`TIMESTAMP`

**Example 1: Hours and Minutes**

Truncate a timestamp to the `hour` date part:

```sql
SELECT DATE_TRUNC('hour', '2016-08-08 12:05:31');

```

```output

+-------------------------------------------+
| DATE_TRUNC('hour', '2016-08-08 12:05:31') |
+-------------------------------------------+
| 2016-08-08 12:00:00                       |
+-------------------------------------------+

```

Truncate a timestamp to the `minute` date part:

```sql
SELECT DATE_TRUNC('minute', '2016-08-08 12:05:31');

```

```output

+---------------------------------------------+
| DATE_TRUNC('minute', '2016-08-08 12:05:31') |
+---------------------------------------------+
| 2016-08-08 12:05:00                         |
+---------------------------------------------+
1 row in set (0.07 sec)

```

**Example 2: Months and Weeks**

Consider the following example table named `dt_orders`:

```sql
SELECT * FROM dt_orders;

```

```output

+----+-------------+--------------+---------------------+
| id | customer_id | order_amount | order_time          |
+----+-------------+--------------+---------------------+
|  5 |      677222 |     19973.03 | 2017-01-12 00:00:00 |
|  2 |      656590 |     13666.29 | 2017-01-05 00:00:00 |
|  4 |      941937 |       720.11 | 2017-01-13 00:00:00 |
|  1 |      656590 |      6700.55 | 2017-01-18 00:00:00 |
|  3 |      941937 |     16478.14 | 2017-01-06 00:00:00 |
+----+-------------+--------------+---------------------+
5 rows in set (0.10 sec)

```

To get the sum of `order_amount` for the month of January 2017:

```sql
SELECT DATE_TRUNC('month', order_time), SUM(order_amount)
FROM dt_orders
GROUP BY 1;

```

```output

+---------------------------------+-------------------+
| DATE_TRUNC('month', order_time) | SUM(order_amount) |
+---------------------------------+-------------------+
| 2017-01-01 00:00:00.000000      |          57538.12 |
+---------------------------------+-------------------+

```

To get the `order_amount` for each week in January 2017:

```sql
SELECT DATE_TRUNC('week', order_time), SUM(order_amount)
FROM dt_orders
GROUP BY 1
ORDER BY 1;

```

```output

+--------------------------------+-------------------+
| DATE_TRUNC('week', order_time) | SUM(order_amount) |
+--------------------------------+-------------------+
| 2017-01-02 00:00:00.000000     |          30144.43 |
| 2017-01-09 00:00:00.000000     |          20693.14 |
| 2017-01-16 00:00:00.000000     |           6700.55 |
+--------------------------------+-------------------+

```

To get the `order_amount` for each week in January 2017 with more descriptive column names:

```sql
SELECT CAST(DATE_TRUNC('week', order_time) AS DATE) AS order_week, SUM(order_amount) AS sum_order_week_amount
FROM dt_orders
GROUP BY 1
ORDER BY 1;

```

```output

+------------+-----------------------+
| order_week | sum_order_week_amount |
+------------+-----------------------+
| 2017-01-02 |              30144.43 |
| 2017-01-09 |              20693.14 |
| 2017-01-16 |               6700.55 |
+------------+-----------------------+

```

## Remarks

The `DATE_TRUNC` function supports improved segment elimination (A process where metadata stored for columnstore segments is used to determine where a segment can match a filter queried at execution time.) when used in `WHERE` clauses. See the following query example:

```sql
SELECT ... FROM table_name WHERE DATE_TRUNC('year', column_name) = '2022-01-01 00:00:00';
```

***

Modified at: May 31, 2023

Source: [/db/v9.1/reference/sql-reference/date-and-time-functions/date-trunc/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/date-trunc/)

(An index of the documentation is available at /llms.txt)