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

On Fri, Oct 9, 2015 at 9:34 AM, <jpruitt(at)doozer(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13670
> Logged by: John Pruitt
> Email address: jpruitt(at)doozer(dot)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