DATE_TRUNC() Function in PostgreSQL

In Section we will learn how to use the PostgreSQL DATE_TRUNC() function. DATE_TRUNC() function is used to truncate a date or timestamp to a specified level of precision. It allows you to round down a date or timestamp to a specific unit, such as year, quarter, month, week, day, hour, etc

Syntax for DATE_TRUNC() Function in PostgreSQL:

unit: Specifies the unit to which you want to truncate the date or timestamp (e.g., ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, etc.).

source: The date or timestamp that you want to truncate.

The values of the unit must be one of the following values:

• century
• decade
• year
• month
• day
• week
• hour
• minute
• second
• microseconds
• milliseconds

PostgreSQL TRUNC() function example

Below are some of the Examples for TRUNC() function in postgresql.

Truncate a timestamp to the beginning of the year: TRUNC() function in postgresql:

In the below Example we will be passing year  as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of  year.

 

SELECT DATE_TRUNC('year', '2024-02-24 15:30:45'::timestamp) AS truncated_date;

This will truncate the timestamp to the beginning of the year 2024

Output:

Truncate a timestamp to the beginning of the Month: TRUNC() function in postgresql:

In the below Example we will be passing month  as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of  Month mentioned in the date.

 

SELECT DATE_TRUNC('month', '2024-02-24 15:30:45'::timestamp) AS truncated_date;

This will truncate the timestamp to the beginning of the Feb 2024

Output:

Truncate a timestamp to the beginning of the week: TRUNC() function in postgresql:

In the below Example we will be passing week  as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of week mentioned in the date.

 

SELECT DATE_TRUNC('week', '2024-02-24 15:30:45'::timestamp) AS truncated_date;

This will truncate the timestamp to the beginning of the week of date mentioned

Output:

Truncate a timestamp to the beginning of day: TRUNC() function in postgresql:

In the below Example we will be passing day as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of day for the mentioned date.

 

SELECT DATE_TRUNC('day', '2024-02-24'::date) AS truncated_date;

This will truncate the timestamp to the beginning of the day

Output:

Truncate a timestamp to the nearest hour: TRUNC() function in postgresql:

In the below Example we will be passing hour as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the nearest hour of the mentioned date.

 

SELECT DATE_TRUNC('hour', '2024-02-24 15:30:45'::timestamp) AS truncated_date;

This will truncate the timestamp to the nearest hour for the mentioned date

Output:

Truncate a timestamp to the beginning of quarter: TRUNC() function in postgresql:

In the below Example we will be passing quarter as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of quarter for the mentioned date.

 

SELECT DATE_TRUNC('quarter', '2024-02-24 15:30:45'::timestamp) AS truncated_date;

This will truncate the timestamp to the beginning of the quarter

Output:

Truncate a timestamp to the beginning of Decade: TRUNC() function in postgresql:

In the below Example we will be passing decade as “unit” argument and timestamp as “source” argument in order to truncate date or timestamp to the beginning of decade for the mentioned date.

 

SELECT DATE_TRUNC('decade', '2024-02-24'::date) AS truncated_date;

This will truncate the timestamp to the beginning of the decade

Output: