Can anyone help me to understand why these are showing different outputs?
SELECT
EXTRACT (epoch FROM ('2021-02-01 00:00:00'::timestamp - '2021-01-01 00:00:00'::timestamp)) / 3600 / 24 as time1,
EXTRACT (epoch FROM age('2021-02-01 00:00:00'::timestamp , '2021-01-01 00:00:00'::timestamp)) / 3600 / 24 as time2
output:
time1 | time2 |
---|---|
31 | 30 |
CodePudding user response:
There is a difference between the subtract operator and the function age(),
per the documentation::
timestamp - timestamp › interval
Subtract timestamps (converting 24-hour intervals into days, similarly to justify_hours())
versus
age ( timestamp, timestamp ) › interval
Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
Example:
SELECT
'2021-02-01 00:00:00'::timestamp - '2021-01-01 00:00:00'::timestamp as interval1,
age('2021-02-01 00:00:00'::timestamp , '2021-01-01 00:00:00'::timestamp) as interval2
interval1 | interval2
----------- -----------
31 days | 1 mon
(1 row)
The second interval is converted to 30 days in the further calculations described in the question.