Wrong results of function age(timestamp, timestamp)

--
David Rader
davidr@openscg.com

On Wed, Oct 5, 2016 at 10:01 AM, mmerta <michal.merta@greycortex.com> wrote:

Hello,

I ecountered strange behaviour of function age(timestamp, timestamp).

Let's say we have two timestamps and we want to compute their age() from
fixed timestamp in past:

SELECT pg_catalog.age('2016-04-30 00:00:01'::TIMESTAMP , '2015-01-01
12:00:00'::TIMESTAMP),
pg_catalog.age('2016-04-29 23:59:59'::TIMESTAMP , '2015-01-01
12:00:00'::TIMESTAMP);

age | age
--------------------------------+--------------------------------
1 year 3 mons 28 days 12:00:01 | 1 year 3 mons 28 days 11:59:59

Results are correct.

If we add '1 day'::interval to original timestamps, both results should be
1
day longer.
Howewer first result is exactly 24 hours longer than it should be:

SELECT pg_catalog.age(('2016-04-30 00:00:01'::TIMESTAMP + '1
day'::INTERVAL), '2015-01-01 12:00:00'::TIMESTAMP),
pg_catalog.age(('2016-04-29 23:59:59'::TIMESTAMP + '1
day'::INTERVAL), '2015-01-01 12:00:00'::TIMESTAMP);
age | age
--------------------------------+--------------------------------
1 year 3 mons 30 days 12:00:01 | 1 year 3 mons 29 days 11:59:59

Am I missing something or is it a bug in function age(timestamp,
timestamp)?

I tested this on postgres versions 9.4.6 and 9.5.3 on CentOS 7, using
timezone = 'Europe/Prague' in postgresql.conf.

Regards
Michal Merta

--
View this message in context: http://postgresql.nabble.com/
Wrong-results-of-function-age-timestamp-timestamp-tp5924559.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

As stated in the docs (
https://www.postgresql.org/docs/9.5/static/functions-datetime.html) age()
behaves differently than the "-" operator:
"The age function returns years, months, days, and hours/minutes/seconds,
performing field-by-field subtraction and then adjusting for negative field
values."
And "

So first, age() compares each field, then adjusts. If you compare age() at
midnight vs. at noon you can see the effect:

select age('2016-04-29', '2015-01-01'), age('2016-04-30', '2015-01-01'),
age('2016-05-01', '2015-01-01'), age('2016-05-02', '2015-01-01')

;

age | age | age |
age

-----------------------+-----------------------+---------------+---------------------

1 year 3 mons 28 days | 1 year 3 mons 29 days | 1 year 4 mons | 1 year 4
mons 1 day

When comparing against noon, age() first does field by field
(which means calculating the year, month, day as above) then will adjust
for the negative 12 hours.

select age('2016-04-29', '2015-01-01 12:00:00'::timestamp),
age('2016-04-30', '2015-01-01 12:00:00'::timestamp), age('2016-05-01',
'2015-01-01 12:00:00'::timestamp), age('2016-05-02', '2015-01-01
12:00:00'::timestamp)

;

age | age |
age | age

--------------------------------+--------------------------------+--------------------------------+------------------------

1 year 3 mons 27 days 12:00:00 | 1 year 3 mons 28 days 12:00:00 | 1 year 3
mons 30 days 12:00:00 | 1 year 4 mons 12:00:00
You get the variance in that April is a 30-day month, but May is 31-days,
so when age subtracts 12 hours from 1 year 4 months, it calculates 1 year,
3 months, (31 days - 12 hours) which is 1 year, 3 months, 30 days, 12 hours.

-Dave