---
title: "DATE_TRUNC"
description: "DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data."
canonical: "https://motherduck.com/glossary/date-trunc/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "DuckLake Lakehouse: Getting Started to Going Fast | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-lakehouse-getting-started/"
  - title: "Querying historical data with time travel | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/database-operations/time-travel/"
---

# DATE_TRUNC

> DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data.

## Overview
`DATE_TRUNC` truncates a date, timestamp, or interval to the beginning of a specified precision: `year`, `quarter`, `month`, `week`, `day`, `hour`, `minute`, `second`, and more. It's the standard way to bucket raw event timestamps into daily, weekly, or monthly aggregates.

```sql
SELECT
  DATE_TRUNC('month', order_timestamp) AS order_month,
  SUM(revenue) AS monthly_revenue
FROM orders
GROUP BY ALL
ORDER BY ALL;
```

`DATE_TRUNC('week', ts)` truncates to the most recent Monday (ISO week start), while `DATE_TRUNC('day', ts)` zeroes out the time-of-day portion, leaving midnight.

## DATE_TRUNC vs EXTRACT
`DATE_TRUNC` returns a value of the same type (date/timestamp) rounded down — useful for grouping and joining on time buckets. `EXTRACT` instead pulls out a single numeric field (like the month number) without preserving the full date. Use `DATE_TRUNC` when you need to group by a time period and still display or compare it as a date; use `EXTRACT` when you only need the bare number.

## DuckDB specifics
DuckDB implements `date_trunc('part', value)` matching Postgres semantics, and also accepts the alias `datetrunc()`. It supports the same argument order and part names (`'year'`, `'quarter'`, `'month'`, `'week'`, `'day'`, `'hour'`, `'minute'`, `'second'`, `'milliseconds'`, `'microseconds'`, `'decade'`, `'century'`), and works over `DATE`, `TIMESTAMP`, and `TIMESTAMPTZ` columns. Combined with DuckDB's `GROUP BY ALL`, monthly rollups become compact:

```sql
SELECT DATE_TRUNC('day', event_time) AS day, COUNT(*) AS events
FROM events
GROUP BY ALL
ORDER BY ALL;
```