Basic Syntax Math Functions Date Functions JSON Functions Databases Tables & Schema Management String Functions Triggers Indexes

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)
Offerto da

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');
Offerto da

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');
Offerto da

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;
Offerto da

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.

PostqreSQL