Re: 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