Converting PostgreSQL interval to seconds produces wrong values

Question

I am working with some intervals in Postgresql and I am not sure I understand how intervals work.

What I am trying to do is convert an interval to seconds. So I have the following values:

select extract ('epoch' from '1 year'::INTERVAL);

Produces the number 31557600. If we divide this number by (60*60*24) which is the number of seconds in a day, we get 365.
So a year in a postgresql interval has 365 days.

select extract ('epoch' from '1 month'::INTERVAL);

Produces the number 2592000. If we divide this number by (60*60*24) we get 30.
So a month in postgresql interval has 30 days.

So by that logic, I am trying to calculate the number of seconds in the following interval:

select extract ('epoch' from '2 year 2 month 1 day 10 hour 5 minute'::INTERVAL);

I am using the following formula to calculate the result from the above query:

SELECT (years * 365 * 24 * 3600) + (months * 30 * 24 * 3600) + (days * 24 * 3600) + (hours * 3600) + (minutes * 60);

When we replace the variables with values, the expression looks like this:

SELECT (2 * 365 * 24 * 3600) + (2 * 30 * 24 * 3600) + (1 * 24 * 3600) + (10 * 3600) + (5 * 60);

The problem that I am having is that the result of the first query (SELECT extract...) produces the result 68421900 while the query from the formula above produces the result 68378700.
From my understanding, the results should be identical, but there is 12 hours (43200 seconds) difference between the results. Why is this happening?

It is important to note that if I remove the years from the interval I am getting the same result from both queries, so I guess it has something to do with the years.

Answer 1

I ran into a similar problem and eventually found something that worked.

SELECT AVG( EXTRACT(EPOCH FROM (end_ts - start_ts)) )

produced incorrect values.

SELECT AVG( EXTRACT(EPOCH FROM INTERVAL (end_ts - start_ts)) )

produced a syntax error.

Extracting the epochs separately from the start and stop timestamps and then running the math on them worked.

SELECT AVG( ( EXTRACT(EPOCH FROM end_ts) - EXTRACT(EPOCH FROM start_ts) ) )

I threw in some additional math to translate to hours.

SELECT AVG( ( EXTRACT(EPOCH FROM end_ts) - EXTRACT(EPOCH FROM start_ts) )::FLOAT/86400 )

Answer 2

Your problem is integer arithmetic. The mistake is here:

select extract ('epoch' from '1 year'::INTERVAL);

Produces the number 31557600. If we divide this number by (60*60*24) which is the number of seconds in a day, we get 365.

What you probably did was

SELECT 31557600 / (60 * 60 * 24);

 ?column? 
----------
      365
(1 row)

But the reality is:

SELECT extract (epoch FROM INTERVAL '1 year') / (60 * 60 * 24);

 ?column? 
----------
   365.25
(1 row)

So the length of a year in PostgreSQL is actually 365 days and 6 hours. This is an approximation (the true value is slightly less) and should account for leap years.

---

Note: These values are kind of arbitrary because there is no single correct answer for “how long is one (calendar) month” or “how long is one (calendar) year” — the answer depends on the individual month or year.

If you add an interval to a timestamp with time zone, the result will always be correct, because in that context the exact length is clear.

---

As to the question why a year is assumed to have 365.25 days, while a month has 30 days, here is what the source has to say in src/include/datatype/timestamp.h:

/*
 * Assorted constants for datetime-related calculations
 */

#define DAYS_PER_YEAR   365.25  /* assumes leap year every four years */
#define MONTHS_PER_YEAR 12
/*
 *      DAYS_PER_MONTH is very imprecise.  The more accurate value is
 *      365.2425/12 = 30.436875, or '30 days 10:29:06'.  Right now we only
 *      return an integral number of days, but someday perhaps we should
 *      also return a 'time' value to be used as well.  ISO 8601 suggests
 *      30 days.
 */
#define DAYS_PER_MONTH  30              /* assumes exactly 30 days per month */
#define HOURS_PER_DAY   24              /* assume no daylight savings time changes */

/*
 *      This doesn't adjust for uneven daylight savings time intervals or leap
 *      seconds, and it crudely estimates leap years.  A more accurate value
 *      for days per years is 365.2422.
 */
#define SECS_PER_YEAR   (36525 * 864)   /* avoid floating-point computation */
#define SECS_PER_DAY    86400
#define SECS_PER_HOUR   3600
#define SECS_PER_MINUTE 60
#define MINS_PER_HOUR   60

So I guess the reason is that ISO 8601 decrees that a month has 30 days.

Answer 3

First of all, your statement So a month in postgresql interval has 30 days. is wrong:

janbet=> select '2019-01-01'::date + '1 month'::interval;
      ?column?
---------------------
 2019-02-01 00:00:00
(1 row)

janbet=> select '2019-02-01'::date + '1 month'::interval;
      ?column?
---------------------
 2019-03-01 00:00:00
(1 row)

As you see above, month could be 31 days or 28 days, depending on the context. It is, in fact, a month and months have different lengths.

The same applies to '2 year 2 month 1 day 10 hour 5 minute'::interval:

janbet=> select '2019-01-01'::date + '2 year 2 month 1 day 10 hour 5 minute'::interval - '2019-01-01'::date;
     ?column?
-------------------
 791 days 10:05:00
(1 row)

janbet=> select '2021-01-01'::date + '2 year 2 month 1 day 10 hour 5 minute'::interval - '2021-01-01'::date;
     ?column?
-------------------
 790 days 10:05:00
(1 row)

We get a different result depending on whether those 2 years include leap year or not.

My best guess about your 12 hours is that 2 years include leap year in 50% of cases so this is one day * 1/2. In fact, if you repeat your calculation with '3 years', you get a 18-hour difference, so I'm prette sure it works that way.

I understand that could be surprising, but I can't think of other result that would be consistent with variable lengths of years/months and this is the desired interval behaviour.