Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL DATE_TRUNC

The `DATE_TRUNC` function in PostgreSQL is used to truncate a date or timestamp to a specified precision, such as hour, day, or month. It is useful in scenarios where you need to aggregate data over specific time intervals.

Usage

The `DATE_TRUNC` function is employed when you need to align dates and timestamps to a particular level of precision for analysis or reporting. It is often used in conjunction with aggregate functions to summarize data over set time periods.

sql
DATE_TRUNC('precision', source_date)

In this syntax, `'precision'` specifies the level to which the date will be truncated (e.g., 'hour', 'day'), and `source_date` is the date or timestamp to be truncated. The `source_date` can be a column in a table or any expression that results in a date or timestamp.

Precision Options

PostgreSQL supports various precision levels for truncation, including:

  • `second`
  • `minute`
  • `hour`
  • `day`
  • `week`
  • `month`
  • `quarter`
  • `year`
  • `decade`
  • `century`
  • `millennium`

Examples

1. Truncate to Day

sql
SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-04 15:23:45');

This example truncates the timestamp to the start of the day, resulting in `2023-10-04 00:00:00`. If the source date is already at the specified precision, such as `2023-10-04 00:00:00`, the result remains unchanged.

2. Truncate to Month

sql
SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-04 15:23:45');

Here, the timestamp is truncated to the beginning of the month, returning `2023-10-01 00:00:00`.

3. Truncate for Aggregation

sql
SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(*)
FROM orders
GROUP BY week_start;

This example truncates `order_date` to the start of each week to group orders by weekly intervals, providing a count of orders per week.

Tips and Best Practices

  • Choose the right precision. Select the precision level that aligns with your data analysis needs, such as 'day' for daily summaries or 'month' for monthly trends.
  • Combine with aggregates. Use `DATE_TRUNC` with aggregate functions like `COUNT`, `SUM`, or `AVG` to perform time-based data analysis.
  • Be mindful of time zones. Ensure that your timestamps are in the correct time zone to avoid inaccuracies when truncating. Use `AT TIME ZONE` for explicit time zone handling, e.g., `DATE_TRUNC('day', timestamp_column AT TIME ZONE 'UTC')`.
  • Optimize performance. Truncating large datasets can be resource-intensive; consider indexing your date columns with a B-tree index to improve query performance.