Re: BUG #13670: DST discrepancy between age() and subtraction for timestamptz arguments

From: John Pruitt <jpruitt(at)doozer(dot)com>
To: Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #13670: DST discrepancy between age() and subtraction for timestamptz arguments
Date: 2015-10-12 14:50:18
Message-ID: CACune3za1xvptswi7QxnFNAZaBVgVD6Ldf7iGMe8dDfjy3vjCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Okay, I'll acknowledge that the issue has been discussed before, however I
fail to see how the issue can be considered resolved. The function takes
time zone aware arguments, ignores that input, and returns plainly
incorrect answers - the very definition of a bug.

If you use the age function in any kind of calculation, you'll have
problems. If you use it in calculations for metered billing (as I was),
you'll over charge or under charge your customers.

At the very minimum, the documentation should have a warning in big bold
red letters describing this deficiency.

Does anyone know if any of the other date/time functions exhibit similar
behavior?

John Pruitt
Delivery Director
Doozer Software, Inc.

On Fri, Oct 9, 2015 at 3:35 PM, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
wrote:

>
>
> On Sat, Oct 10, 2015 at 3:01 AM, John Pruitt <jpruitt(at)doozer(dot)com> wrote:
> > On further inspection, an age function that explicitly takes timestamptz
> > arguments does in fact exist.
> >
> > select ns.nspname || '.' || proname || '(' ||
> oidvectortypes(proargtypes) ||
> > ')'
> > from pg_proc
> > inner join pg_namespace ns on (pg_proc.pronamespace = ns.oid)
> > where proname = 'age'
> > ;
> >
> > pg_catalog.age(xid)
> > pg_catalog.age(timestamp without time zone)
> > pg_catalog.age(timestamp with time zone)
> > pg_catalog.age(timestamp without time zone, timestamp without time zone)
> > pg_catalog.age(timestamp with time zone, timestamp with time zone)
> >
> > Shouldn't the versions that take timestamp with time zone honor the time
> > zone?
>
> Thanks for providing more details and your analysis.
> But in function timestamptz_age which accepts timestamptz as arguments has
> the following comment in the code.
>
> /*
> * Note: we deliberately ignore any difference between tz1 and tz2.
> */
>
> The following mail provides the details of timezone ignorance in age
> function.
> http://www.postgresql.org/message-id/8907.1101918113@sss.pgh.pa.us
>
> Because of this reason, the age function works similar to timestamp even
> if the given input is timestamptz.
>
> Regards,
> Hari Babu
> Fujitsu Australia
>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message postgresql 2015-10-12 17:47:31 BUG #13674: psql: \i from a script run through \e misparses SQL as \i arguments
Previous Message Andres Freund 2015-10-12 13:43:28 Re: BUG #13672: What is the purpose of the temp_buffers setting?