date_truncFunctions are used to truncate (round down) date or timestamp values to a specified level of precision in PostgreSQL. This function is useful when you want to ignore smaller units of time (e.g. hours, minutes, seconds) and focus on larger units (e.g. days, months, years).date_truncThe syntax of is as follows:
date_trunc(unit, source);
-
unit: Specifies the time unit to which the source value is to be truncated. It can be one of the following:-
'microseconds'(microseconds) -
'milliseconds'(millisecond) -
'second'or'seconds'(Second) -
'minute'or'minutes'(minute) -
'hour'or'hours'(Hour) -
'day'or'days'(sky) -
'week'or'weeks'(week) -
'month'or'months'(moon) -
'quarter'or'quarters'(Quarterly) -
'year'or'years'(Year)
-
By specifyingunit, you can putsourceThe value of is truncated to the required time accuracy. Here are some examples:
-- Cut the timestamp to minutes
SELECT date_trunc('minute', current_timestamp);
-- 2024-01-17 08:08:00
--
-- Cut the timestamp to the hour
SELECT date_trunc('hour', current_timestamp);
-- 2024-01-17 08:00:00
--
-- Cut the timestamp to the day
SELECT date_trunc('day', current_timestamp);
-- 2024-01-17 00:00:00
--
-- Cut the timestamp to the month
SELECT date_trunc('month', TIMESTAMP '2024-02-16 14:32:45');
-- 2024-02-01 00:00:00
--
-- Cut the timestamp to year
SELECT date_trunc('year', TIMESTAMP '2024-02-16 14:32:45');
-- 2024-01-01 00:00:00
--
-- The day begins
SELECT date_trunc('day', current_timestamp);
--
-- The end of the day
SELECT date_trunc('day', current_timestamp) + INTERVAL '1 day' - interval '1 second';
These queries will return a date or time stamp truncated to the specified unit。Replenish:
me=# select date_trunc('day',date '2018-05-15 09:00:00+08') + interval '9 h';
?column?
------------------------
2018-05-15 09:00:00+08
(1 Line records)
me=# select date_trunc('day',date '2018-05-15 14:09:04.127444+08') + interval '9 h';
?column?
------------------------
2018-05-15 09:00:00+08
(1 Line records)
me=# select date_trunc('day',date '2018-05-14 14:09:04.127444+08') + interval '9 h';
?column?
------------------------
2018-05-14 09:00:00+08
(1 Line records)
me=# select date_trunc('day',time '2018-05-14 14:09:04.127444+08') + interval '9 h';
?column?
----------
09:00:00
(1 Line records)
me=# select date_trunc('day',timestamp '2018-05-14 14:09:04.127444+08') + interval '9 h';
?column?
---------------------
2018-05-14 09:00:00
(1 Line records)
me=# select date_trunc('day',timestamptz '2018-05-14 14:09:04.127444+08') + interval '9 h';
?column?
------------------------
2018-05-14 09:00:00+08
(1 Line records)
me=# select date_trunc('day',timestamptz '2018-05-14 14:09:04.127444+08'+ interval '9 h');
date_trunc
------------------------
2018-05-14 00:00:00+08
(1 Line records)
Summarize
This is the end of this article about the syntax and some examples of the date_trunc function in PostgreSQL. For more related content of the PGSQL date_trunc function, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!