Introduction to the PostgreSQL DATE_TRUNC() function

TheDATE_TRUNC() function truncates aTIMESTAMP, aTIMESTAMP WITH TIME ZONE, or an  INTERVAL value to a specified precision.

Here’s the basic syntax of theDATE_TRUNC function:

DATE_TRUNC(field, source [,time_zone])

In this syntax:

source

source is a value or an expression of type timestamp, timestamp with time zone, or interval. If you use a value of the date or time type, the function will cast it automatically to timestamp or interval respectively.

field

field specifies the to which precision to truncate thesource.

Here are the valid values for thefield:

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

time_zone

time_zone specifies the time zone in which the function will perform the truncation. Thetime_zone argument is the default.

If you omit thetime_zone, the function will truncate thesource based on the current time zone setting.

TheDATE_TRUNC function returns aTIMESTAMP or anINTERVAL value.

PostgreSQL DATE_TRUNC() function examples

Let’s explore some examples of using theDATE_TRUNC() function.

1) Basic PostgreSQL DATE_TRUNC() function example

The following example uses theDATE_TRUNC() function to truncate aTIMESTAMP value tohour part:

SELECT DATE_TRUNC('hour',TIMESTAMP '2017-03-17 02:09:30');

Output:

date_trunc--------------------- 2017-03-17 02:00:00(1 row)

In this example, theDATE_TRUNC() function returns a timestamp with the hour precision.

If you want to truncate aTIMESTAMP value to a minute, you use the'minute' field as shown in the following example:

SELECT DATE_TRUNC('minute',TIMESTAMP '2017-03-17 02:09:30');

The function returns aTIMESTAMP with the precision is minute:

date_trunc--------------------- 2017-03-17 02:09:00(1 row)

2) Using PostgreSQL DATE_TRUNC() function with table data

See the followingrental table in thesample database:

The following example uses theDATE_TRUNC() function to retrieve the number of rentals by month from the rental table:

SELECT    DATE_TRUNC('month', rental_date) m,    COUNT (rental_id)FROM    rentalGROUP BY    mORDER BY    m;

Output:

m          | count---------------------+------- 2005-05-01 00:00:00 |  1156 2005-06-01 00:00:00 |  2311 2005-07-01 00:00:00 |  6709 2005-08-01 00:00:00 |  5686 2006-02-01 00:00:00 |   182(5 rows)

This query retrieves the month of each rental date and counts the number of rentals each month from therental table. It then groups the counts by month and sorts the result set by month.

If you want to count the rentals by week, you can pass the week to the DATE_TRUNC() function as follows:

SELECT    DATE_TRUNC('week', rental_date)week,    COUNT (rental_id)FROM    rentalGROUP BY    weekORDER BY    week;

Output:

week         | count---------------------+------- 2005-05-23 00:00:00 |   835 2005-05-30 00:00:00 |   321 2005-06-13 00:00:00 |  1705 2005-06-20 00:00:00 |   606 2005-07-04 00:00:00 |  2497 2005-07-11 00:00:00 |   956 2005-07-25 00:00:00 |  3256 2005-08-01 00:00:00 |  1314 2005-08-15 00:00:00 |  3148 2005-08-22 00:00:00 |  1224 2006-02-13 00:00:00 |   182(11 rows)

The following example uses theDATE_TRUNC() function to count the number of rentals by staff per year:

SELECTstaff_id,date_trunc('year', rental_date) y,COUNT (rental_id) rentalFROMrentalGROUP BYstaff_id, yORDER BYstaff_id;

Output:

staff_id |          y          | rental----------+---------------------+--------        1 | 2006-01-01 00:00:00 |     85        1 | 2005-01-01 00:00:00 |   7955        2 | 2006-01-01 00:00:00 |     97        2 | 2005-01-01 00:00:00 |   7907(4 rows)

Summary

• Use the PostgreSQLDATE_TRUNC function to truncate a timestamp or an interval value to a specified level of precision

Last updated onMarch 22, 2024

On this page

• Introduction to the PostgreSQL DATE_TRUNC() function
• PostgreSQL DATE_TRUNC() function examples
• Summary