PostgreSQL DATE_TRUNC Function

Summary: this tutorial shows you how to use the PostgreSQL date_trunc() function to truncate a timestamp or interval to a specified level of precision.

Introduction to the PostgreSQL date_trunc function

The date_trunc function truncates a TIMESTAMP or an  INTERVAL value based on a specified date part e.g., hour, week, or month and returns the truncated timestamp or interval with a level of precision.

The following illustrates the syntax of the date_trunc function:

date_trunc('datepart', field)
Code language: SQL (Structured Query Language) (sql)

The datepart argument is the level of precision used to truncate the field, which can be one of the following:

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

The field argument is a TIMESTAMP or an INTERVAL value to truncate. It could be an expression that evaluates to a timestamp or an interval.

The date_trunc function returns a TIMESTAMP or an INTERVAL value.

PostgreSQL date_trunc examples

The following example truncates a TIMESTAMP value to hour date part:

SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30');
Code language: SQL (Structured Query Language) (sql)

The following is the output:

     date_trunc
---------------------
 2017-03-17 02:00:00
(1 row)
Code language: SQL (Structured Query Language) (sql)

The date_trunc function returns a result with the hour precision.

If you want to truncate a TIMESTAMP value to a minute, you pass the 'minute' string as the first argument:

SELECT date_trunc('minute', TIMESTAMP '2017-03-17 02:09:30');
Code language: SQL (Structured Query Language) (sql)

The function returns a TIMESTAMP whose precision level is minute:

 date_trunc
---------------------
 2017-03-17 02:09:00
(1 row)
Code language: SQL (Structured Query Language) (sql)

See the following rental table in the sample database:

You  can count the number of rentals by month by using the date_trunc function as follows:

SELECT
    date_trunc('month', rental_date) m,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    m
ORDER BY
    m;
Code language: SQL (Structured Query Language) (sql)

In this query, the date_trunc function truncates the rental date to month part. The COUNT function counts the number of rentals and the GROUP BY clause groups the rentals by month.

        month        | 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)
Code language: SQL (Structured Query Language) (sql)

Similarly, you can count the number of rentals by staff per year as follows:

SELECT
	staff_id,
	date_trunc('year', rental_date) y,
	COUNT (rental_id) rental
FROM
	rental
GROUP BY
	staff_id, y
ORDER BY
	staff_id
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

 staff_id |          y          | rental
----------+---------------------+--------
        1 | 2006-01-01 00:00:00 |     85
        1 | 2005-01-01 00:00:00 |   7955
        2 | 2005-01-01 00:00:00 |   7907
        2 | 2006-01-01 00:00:00 |     97
(4 rows)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the PostgreSQL date_trunc function to truncate a timestamp or an interval value.