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?
Thank you.
John Pruitt
Delivery Director
Doozer Software, Inc.
jpruitt@doozer.com
work 205-413-8313
cell 205-746-7464
On Fri, Oct 9, 2015 at 2:53 AM, Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:
>
> On Fri, Oct 9, 2015 at 9:34 AM, <jpruitt@doozer.com> wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 13670
> > Logged by: John Pruitt
> > Email address: jpruitt@doozer.com
> > PostgreSQL version: 9.4.4
> > Operating system: x86_64-apple-darwin
> > Description:
> >
> > We are seeing a discrepancy between what is returned by the
> age(timestamptz,
> > timestamptz) function versus using the subtraction operator (timestamptz
> -
> > timestamptz) on the DST transition days. It appears that the subtraction
> > operator gives the correct answers, while the age function does not.
> >
> > /* short day - 2:00 is skipped - 1 hour is correct */
> > select
> > '2015-03-08 03:00'::timestamptz - '2015-03-08 01:00'::timestamptz -- 1
> > hour
> > , age('2015-03-08 03:00'::timestamptz, '2015-03-08 01:00'::timestamptz)
> -- 2
> > hours
> > ;
> >
> > /* long day - 1:00 repeats - 3 hours is correct */
> > select
> > '2014-11-02 02:00'::timestamptz - '2014-11-02 00:00'::timestamptz -- 3
> > hours
> > , age('2014-11-02 02:00'::timestamptz, '2014-11-02 00:00'::timestamptz)
> -- 2
> > hours
> > ;
>
> From the PostgreSQL documentation it shows that the age function works
> with timestamp
> agruements instead of timestamptz. So the behavior is correct as it is
> ignoring the timezone
> effect.
>
> Because of default cast functions for timestamp and timestamptz, the
> function can accept
> any type of argument and works as per timestamp datatype described in the
> documentation.
>
> Refer: Date/Time Functions
> http://www.postgresql.org/docs/9.0/static/functions-datetime.html
>
> Because of the above reason, it works similar like as follows.
>
> select
> '2014-11-02 02:00'::timestamp - '2014-11-02 00:00'::timestamp
> , age('2014-11-02 02:00'::timestamp, '2014-11-02 00:00'::timestamp)
>
>
> From the code point of view, it just accepts the data timestamptz and just
> ignores the
> timezone in the calculation according to the documentation.
>
> Regards,
> Hari Babu
> Fujitsu Australia
>
