PostgreSQL DATE_TRUNC() Function

  1. the Use of the DATE_TRUNC() Function in PostgreSQL
  2. "millisecond" Precision in Postgres DATE_TRUNC()
  3. "second" Precision in Postgres DATE_TRUNC()
  4. "minute" Precision in Postgres DATE_TRUNC()
  5. "hour" Precision in Postgres DATE_TRUNC()
  6. "day" Precision in Postgres DATE_TRUNC()
  7. Use DATE_TRUNC() With Query in Postgres

This article will discuss Postgres’s DATE_TRUNC() function with code snippets.

the Use of the DATE_TRUNC() Function in PostgreSQL

It’s possible in Postgres to truncate or round a given timestamp to some given level of precision. Say, you can truncate it to the nearest minute, hour, day, month, etc.

In Postgres, DATE_TRUNC() has the following intervals.

Century
Day
Decade
Hour
Minute
Microsecond
Millisecond
Second
Month
Quarter
Week
Year

Here’s the current timestamp. We’ll use it for different intervals to see the result.

postgres=# SELECT NOW();
              now
-------------------------------
 2022-04-29 17:30:48.256668+06
(1 row)

"millisecond" Precision in Postgres DATE_TRUNC()

We will use “millisecond” as precision in the DATE_TRUNC().

postgres=# SELECT DATE_TRUNC('millisecond', TIMESTAMP '2022-04-29 17:30:48.256668');
       date_trunc
-------------------------
 2022-04-29 17:30:48.256
(1 row)

The millisecond was 256668. Then it was truncated to 256.

"second" Precision in Postgres DATE_TRUNC()

Here, we use "second" as precision in the DATE_TRUNC().

postgres=# SELECT DATE_TRUNC('second', TIMESTAMP '2022-04-29 17:30:48.256668');
     date_trunc
---------------------
 2022-04-29 17:30:48
(1 row)

You can see that the decimal part is trimmed to round up the value.

"minute" Precision in Postgres DATE_TRUNC()

We used the “minute” as precision in the DATE_TRUNC().

postgres=# SELECT DATE_TRUNC('minute', TIMESTAMP '2022-04-29 17:30:48.256668');
     date_trunc
---------------------
 2022-04-29 17:30:00
(1 row)

The minute was 30:48. Using the DATE_TRUNC(), it became 30:00.

"hour" Precision in Postgres DATE_TRUNC()

Let’s use "hour" as precision in the DATE_TRUNC().

postgres=# SELECT DATE_TRUNC('hour', TIMESTAMP '2022-04-29 17:30:48.256668');
     date_trunc
---------------------
 2022-04-29 17:00:00
(1 row)

You can see the difference between 17:30:48.256668 and 17:00:00.

"day" Precision in Postgres DATE_TRUNC()

Use the "day" as precision in the DATE_TRUNC().

postgres=# SELECT DATE_TRUNC('day', TIMESTAMP '2022-04-29 17:30:48.256668');
     date_trunc
---------------------
 2022-04-29 00:00:00
(1 row)

You can see that the time became 00:00:00. It rounded up the day.

Use DATE_TRUNC() With Query in Postgres

Let’s have the following MOCK_DATA on hotel check-in.

postgres=# SELECT * from MOCK_DATA;
 id  | first_name  |  last_name   |              email               |   gender    |  check_in
-----+-------------+--------------+----------------------------------+-------------+------------
   1 | Agustin     | Bawdon       | abawdon0@umn.edu                 | Male        | 2016-05-11
   2 | Maximilian  | Ber          | mber1@artisteer.com              | Male        | 2013-03-08
   3 | Randy       | Kline        | rkline2@pcworld.com              | Bigender    | 2019-01-01
   4 | Bonnibelle  | Mazillius    | bmazillius3@is.gd                | Female      | 2011-07-06
   5 | Rolland     | Hollidge     | rhollidge4@seesaa.net            | Male        | 2009-04-30
   6 | Sandro      | Hubbert      | shubbert5@nature.com             | Male        | 2019-11-26
   7 | Collin      | La Torre     | clatorre6@google.de              | Male        | 2016-02-12
   8 | Joleen      | Jerram       | jjerram7@instagram.com           | Female      | 2015-05-23
-- More  --

We want to count each year’s check-ins.

SELECT
    date_trunc('year', check_in) year_no,
    COUNT(id) Customers
FROM
    MOCK_DATA
GROUP BY
    year_no
ORDER BY
    year_no;

Output:

        year_no         | customers
------------------------+-----------
 2008-01-01 00:00:00+06 |        17
 2009-01-01 00:00:00+06 |        20
 2010-01-01 00:00:00+06 |        15
 2011-01-01 00:00:00+06 |        20
 2012-01-01 00:00:00+06 |        19
 2013-01-01 00:00:00+06 |        18
 2014-01-01 00:00:00+06 |        13
 2015-01-01 00:00:00+06 |        24
 2016-01-01 00:00:00+06 |        18
 2017-01-01 00:00:00+06 |        13
 2018-01-01 00:00:00+06 |        23
 2019-01-01 00:00:00+06 |        17
 2020-01-01 00:00:00+06 |        16
 2021-01-01 00:00:00+06 |        12
 2022-01-01 00:00:00+06 |         5
(15 rows)

To know more about DATE_TRUNC(), here’s the official documentation. You can use mockaroo to generate the example database.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL Function

  • NEXTVAL Function in PostgreSQL
  • PostgreSQL DATEDIFF Function