Re: BUG #13670: DST discrepancy between age() and subtraction for timestamptz arguments - Mailing list pgsql-bugs

Firstly, thank your for the response and clarification.

I'd like to suggest that the documentation you referred to be clarified.
The description at the top of the page says:

All the functions and operators described below that take time or
timestamp inputs
> actually come in two variants: one that takes time with time zone or time=
stamp
> with time zone, and one that takes time without time zone or timestamp
> without time zone. For brevity, these variants are not shown separately.
> Also, the + and * operators come in commutative pairs (for example both
> date + integer and integer + date); we show only one of each such pair.


Unless I'm interpreting this incorrectly, this does not appear to be true
for the age function. Are there other functions and operators on the page
for which the overloaded variants also do not exist?

Thanks again,=E2=80=8B


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 (timestampt=
z
> -
> > timestamptz) on the DST transition days. It appears that the subtractio=
n
> > 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 jus=
t
> ignores the
> timezone in the calculation according to the documentation.
>
> Regards,
> Hari Babu
> Fujitsu Australia
>