If I run this query on my database

SELECT EXTRACT('epoch' FROM age('2021-01-01'::date, '2019-12-01'::date))

The result is 34149600.

But then if I try with 2019-12-02 (one day more)

SELECT EXTRACT('epoch' FROM age('2021-01-01'::date, '2019-12-02'::date))

The result is exactly the same 34149600!

As if the seconds passed from 02 december 2019 to 01 january 2021 are the same as the seconds passed from 01 december 2019 to 01 january 2021.

Why is this? I've already tried the above code with timezones on 00:00:00 00 timezone for all dates (for 1st january 2021, 1st december 2019 and 2nd december 2021), and it gives the same result

Obviously, I would have expected the epoch to be different, around 3600*24 of difference (seconds in a day).

CodePudding user response：

The similar results come from the age() function which returns an interval with years, months, days. In such an interval, 1 month = 30 days, so their conversions in seconds are similar.

You will get the expected result with

SELECT extract(epoch from  ('2021-01-01'::timestamp - '2019-12-01'::timestamp)) => 34300800.000000
SELECT extract(epoch from  ('2021-01-01'::timestamp - '2019-12-02'::timestamp)) => 34214400.000000