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.haribabu@gmail.com>
wrote:
>
>
> On Sat, Oct 10, 2015 at 3:01 AM, John Pruitt <jpruitt@doozer.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
>
