PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
    • Aggregate Functions
    • Date / Time Functions
    • String Functions
    • Math Functions
Home / PostgreSQL Date Functions / PostgreSQL DATE_TRUNC Function

PostgreSQL DATE_TRUNC Function

Summary: this tutorial shows you how to use the PostgreSQL date_trunc function to truncates 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:

1
date_trunc('datepart', field)

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:

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

The following is the output:

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

The date_trunc function returns a result with the hour precision.

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

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

The function returns a TIMESTAMP whose precision level is minute:

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

See the following rental table in the sample database:

Rental table - PostgreSQL date_trunc function demo

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

1
2
3
4
5
6
7
8
9
SELECT
    date_trunc('month', rental_date) m,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    m
ORDER BY
    m;

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

1
2
3
4
5
6
7
8
        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)

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

1
2
3
4
5
6
7
8
9
10
SELECT
staff_id,
date_trunc('year', rental_date) y,
COUNT (rental_id) rental
FROM
rental
GROUP BY
staff_id, y
ORDER BY
staff_id

The following shows the output:

1
2
3
4
5
6
7
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)

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

Previous Tutorial: PostgreSQL NOW Function

PostgreSQL Date Functions

  • AGE
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • EXTRACT
  • LOCALTIME
  • LOCALTIMESTAMP
  • DATE_PART
  • DATE_TRUNC
  • NOW
  • TO_DATE
  • TO_TIMESTAMP

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL ANY Operator
  • PostgreSQL EXISTS
  • How To Delete Duplicate Rows in PostgreSQL
  • PostgreSQL TO_CHAR Function
  • PostgreSQL TO_NUMBER Function
  • PostgreSQL TO_TIMESTAMP Function
  • PostgreSQL CEIL Function
  • PostgreSQL MOD Function
  • PostgreSQL FLOOR Function
  • PostgreSQL ABS Function

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.