=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> select extract (epoch from age ('2018-01-31'::date,'2013-01-01'::date) ) /
> 86400,'2018-01-31'::date-'2013-01-01'::date
> first column is wrong calculation
No, it isn't; you're just using the feature incorrectly.
If you want to know the number of days between two dates, date subtraction
is the operation to use. age() has a different purpose, which can be
more clearly seen by looking at its actual result:
select age ('2018-01-31'::date,'2013-01-01'::date);
age
-----------------
5 years 30 days
(1 row)
For certain operations, that's a very useful representation of the
difference between two dates. For example you could add the result
to another date to get a similarly-related date:
select '2012-01-01'::date + '5 years 30 days'::interval;
?column?
---------------------
2017-01-31 00:00:00
(1 row)
You couldn't replicate this by adding a number of days, because
in this example there's a different number of leap days in between.
As for the fractional result from extract(), there's not much
it can really do, because per the above example '5 years 30 days'
doesn't represent a fixed number of seconds. It's approximating
the result as 365.25 days per year (and ignoring issues like
DST, too). Personally I'd never use extract(epoch) on an interval
containing day, month, or year fields, because the result isn't
terribly meaningful --- we only offer that calculation because
the SQL spec says we should.
In short, different operators with different purposes may well
yield different answers. We could wish that the civil calendar
system were less screwy and easier to calculate in, but Postgres
can't fix that :-(
regards, tom lane