1

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).

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Eugenio.Gastelum96
  • 164
  • 1
  • 13
  • 1
    [This](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=523cc7ed893a03b1107fce22bbd34fa1) should give you a hint. See also [this comment](https://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres#comment77606009_952600) – Bergi Dec 04 '21 at 22:27
  • I think it is related to how a month and 30 days are interpreted as same. I mean,, one of them is '1 year 1 mon' and the other is '1 year 30 days'. Strip year and think '1 mon' and '30 days' converted to seconds. Wouldn't you expect the same 30 * 86400? IOW what should '1 mon' be treated as in terms of days. – Cetin Basoz Dec 04 '21 at 22:28
  • You can calculate the difference in days: `'2021-01-01'::date - '2019-12-01'::date` yields `397` and `'2021-01-01'::date - '2019-12-02'::date` yields `396` –  Dec 04 '21 at 23:05

1 Answers1

1

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
Edouard
  • 6,577
  • 1
  • 9
  • 20