pá 26. 6. 2020 v 7:29 odesílatel Thomas Kellerer <shammat@xxxxxxx> 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)
┌────────────────────────────────┐
│ 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?
Regards
Pavel
Thomas
