How to PostgreSQL DATE_TRUNC() Function
-
the Use of the
DATE_TRUNC()
Function in PostgreSQL -
"millisecond"
Precision in PostgresDATE_TRUNC()
-
"second"
Precision in PostgresDATE_TRUNC()
-
"minute"
Precision in PostgresDATE_TRUNC()
-
"hour"
Precision in PostgresDATE_TRUNC()
-
"day"
Precision in PostgresDATE_TRUNC()
-
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.