Functions for manipulating date-time values
function date_trunc() returns plain timestamp | timestamptz | interval
The date_trunc() built-in function truncates a date_time value to the specified granularity. It has three overloads. Each returns a value whose data type is the same as that of the second argument. The first argument, chosen from a list of legal values, determines how the second argument will be truncated. Here is the interesting part of the output from \df date_trunc():
Result data type | Argument data types
-----------------------------+-----------------------------------
interval | text, interval
timestamp with time zone | text, timestamp with time zone
timestamp without time zone | text, timestamp without time zone
Here are the legal values for the first argument, in order of increasing size:
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium
If a date value is used for the second argument, then the typecasting rules presented in the summary table in the section Typecasting between values of different date-time data types are used. However, a date value can be typecast either to a plan timestamp value or to a timestamptz value; so a rule governs the data type of the return value. It can be determined by experiment like this:
set timezone = 'UTC';
with c as (
select date_trunc('year', '2021-07-19'::date) as v)
select pg_typeof(v)::text as "type", v::text from c;
This is the result:
type | v
--------------------------+------------------------
timestamp with time zone | 2021-01-01 00:00:00+00
Of course, you should never rely on implicit typecasting but, rather, should always write the typecast that you intend explicitly. Try this:
with c as (
select date_trunc('year', ('2021-07-19'::date)::timestamp) as v)
select pg_typeof(v)::text as "type", v::text from c;
This is the result:
type | v
-----------------------------+---------------------
timestamp without time zone | 2021-01-01 00:00:00
A plain time value cannot be typecast to a plain timestamp value or to a timestamptz value. But it can be typecast to an interval value. Try this:
with c as (
select date_trunc('hour', ('13:42:37.123456'::time)::interval) as v)
select pg_typeof(v)::text as "type", v::text from c;
This is the result:
type | v
----------+----------
interval | 13:00:00
Notice that when a hybrid interval value is supplied to date_trunc(), the result might surprise the uninitiated. Try this:
with c as (
-- 5000000 hours is close to 600 years.
select make_interval(hours=>5000000) as i)
select i, date_trunc('years', i) as "result" from c;
This is the result:
i | result
---------------+----------
5000000:00:00 | 00:00:00
The result (truncating a value of close to 600 years to a granularity of one year gets a result of zero) emphasizes the point that hybrid interval values can be dangerous. This is explained in the section Custom domain types for specializing the native interval functionality.
function justify_days() | justify_hours() | justify_interval returns interval
Briefly, these functions manipulate the fields of the internal [mm, dd, ss] representation of an interval value by using a rule of thumb to compute and apply increments to the mm and dd fields from, respectively, the dd and ss fields. Nominally, they make interval values easier to comprehend. Here's an example:
\x on
with
c1 as (
select
'2017-05-16 12:00:00'::timestamp as t,
make_interval(days=>1000000, secs=>1000000) as i),
c2 as (
select
t as t,
i as i,
justify_interval(i) as j
from c1)
select
t ::text as "t",
i ::text as "i",
(t + i) ::text as "t + i",
j ::text as "justify_interval(i)",
(t + j) ::text as "t + justify_interval(i)"
from c2;
\x off
This is the result:
t | 2017-05-16 12:00:00
i | 1000000 days 277:46:40
t + i | 4755-04-25 01:46:40
justify_interval(i) | 2777 years 9 mons 21 days 13:46:40
t + justify_interval(i) | 4795-03-10 01:46:40
Notice that justify_interval(i) changed the addition semantics of i. This is dramatically different from justifying, for example, 18 inches to 1 foot 6 inches where the two presentation forms have identical distance semantics.
The section Custom domain types for specializing the native interval functionality explains the potential dangers (dramatically changing the semantics of interval arithmetic) that using these functions brings.
These three functions are described in the section The justify_hours(), justify_days(), and justify_interval() built-in functions within the section The justify() and extract(epoch ...) functions for interval values.