Re: timestamp - timestamp result

pá 26. 6. 2020 v 7:29 odesílatel Thomas Kellerer <shammat(at)gmx(dot)net> napsal:

> I regularly see people suggesting to use
>
> extract(day from one_timestamp - other_timestamp)
>
> to calculate the difference between two timestamps in days.
>
> But I wonder if the "format" of the resulting interval is guaranteed to
> only have days
> (and not months or years)
>
> The following:
>
> timestamp '2020-06-26 17:00:00' - timestamp '2019-04-01 14:00:00'
>
> returns an interval like this:
>
> 0 years 0 mons 452 days 3 hours 0 mins 0.0 secs
>
> However, is there ever a chance that the expression will yield the
> (equivalent) interval:
>
> 1 years 2 mons 25 days 3 hours 0 mins 0.0 secs
>

postgres=# select age(timestamp '2020-06-26 17:00:00',timestamp '2019-04-01
14:00:00');
┌────────────────────────────────┐
│ age │
╞════════════════════════════════╡
│ 1 year 2 mons 25 days 03:00:00 │
└────────────────────────────────┘
(1 row)

>
> e.g. as the age() function does.
>
> Is it safe to assume that "timestamp - timestamp" will never contain units
> larger then days?
>

Now, this operator internally calls only interval_justify_hours functions.
So if somebody doesn't change related code, you can expect so only days,
hours field's are changed.

Regards

Pavel

>
>
> Thomas
>
>
>
>