DATE_TRUNC for rounding dates in PostgreSQL

Introduction

DATE_TRUNC helps you round a date or time to a specific part, like the start of the day or month. It makes dates simpler to compare or group.

When you want to find all records from the start of each month.

When you need to group sales by week, ignoring exact days.

When you want to compare events by hour, ignoring minutes and seconds.

When you want to reset a timestamp to the start of the day for easier filtering.

Syntax

PostgreSQL

DATE_TRUNC('part', timestamp)

The 'part' is the part of the date/time you want to keep, like 'day', 'month', or 'hour'.

The timestamp is the date or time value you want to round.

Examples

Rounds the timestamp to the start of the day (midnight).

PostgreSQL

SELECT DATE_TRUNC('day', TIMESTAMP '2024-06-15 14:23:45');

Rounds the timestamp to the first day of the month at midnight.

PostgreSQL

SELECT DATE_TRUNC('month', TIMESTAMP '2024-06-15 14:23:45');

Rounds the timestamp to the start of the hour (14:00:00).

PostgreSQL

SELECT DATE_TRUNC('hour', TIMESTAMP '2024-06-15 14:23:45');

Sample Program

This query shows how the same timestamp is rounded to the start of the day, month, and hour.

PostgreSQL

SELECT
  DATE_TRUNC('day', TIMESTAMP '2024-06-15 14:23:45') AS day_start,
  DATE_TRUNC('month', TIMESTAMP '2024-06-15 14:23:45') AS month_start,
  DATE_TRUNC('hour', TIMESTAMP '2024-06-15 14:23:45') AS hour_start;

Important Notes

DATE_TRUNC always returns a timestamp rounded down to the specified part.

You can use parts like 'second', 'minute', 'hour', 'day', 'week', 'month', 'year', and more.

It is very useful for grouping data by time periods in reports.

Summary

DATE_TRUNC rounds dates or times to a chosen part like day or month.

It helps simplify and group date/time data.

Use it when you want to ignore smaller parts of a timestamp.