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

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
;

The timezone setting in the database is 'America/Chicago' which is the same
setting in the operating system.

show timezone; -- 'America/Chicago'

version():

version
"PostgreSQL 9.4.4 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.11.00), 64-bit"

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

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
>

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
>

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

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
>

On Tue, Oct 13, 2015 at 1:50 AM, John Pruitt <jpruitt@doozer.com> wrote:
> 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.

I also feel like that it is a bug. Here I attached a patch that
corrects the problem.
The patch just adds the difference in DST to the hours of difference
between the two
timestamp. The drawback in this approach is that, it produces the result as
24:00:00 instead of 1 day.

Any better approach to handle this problem?

Regards,
Hari Babu
Fujitsu Australia

Haribabu Kommi <kommi.haribabu@gmail.com> writes:
> On Tue, Oct 13, 2015 at 1:50 AM, John Pruitt <jpruitt@doozer.com> wrote:
>> 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.

> I also feel like that it is a bug. Here I attached a patch that
> corrects the problem.

I think you both are fundamentally missing the point.

The age() function exists to provide a "symbolic" difference between two
timestamps.  In its intended use-case, the difference between midnight
on March 1 2015 and midnight on April 1 2015 is one month.  Not one month
plus or minus an hour.

If you want the other behavior, why aren't you using plain timestamp
subtraction?

regression=# select age('1 april 2015'::timestamptz, '1 march 2015'::timestamptz);
  age
-------
 1 mon
(1 row)

regression=# select '1 april 2015'::timestamptz - '1 march 2015'::timestamptz;
     ?column?
------------------
 30 days 23:00:00
(1 row)

(This is based on US DST rules, you might get different results in other
timezones.)

Even discounting any thought of backwards compatibility, it does not seem
to me that erasing the distinction between these behaviors is a good
thing.  People might legitimately want either one.

Another point worth considering is this:

regression=# select '1 march 2015'::timestamptz + age('1 april 2015'::timestamptz, '1 march 2015'::timestamptz);
        ?column?
------------------------
 2015-04-01 00:00:00-04
(1 row)

While I'm not sure that "X + age(Y, X) = Y" holds universally, it does
hold in this example, and the proposed patch would break that.

Having said that, I notice that the seemingly even more obvious
identity "X + (Y - X) = Y" doesn't work in this case.  Maybe we should do
something about that, or maybe not.  My point is mainly that there are
a *lot* of moving parts in this area, as well as a considerable amount
of backwards-compatibility history that we must not take lightly.  It is
well to remember also that civil time and DST laws were written by
politicians who have never heard of mathematical consistency.

            regards, tom lane

>
> I think you both are fundamentally missing the point.


Yes, I clearly missed it. I didn't understand what was meant by "symbolic".
Maybe the docs could be clarified a bit; the words "imprecise" and "display
purposes" come to mind.

If that is indeed the purpose of age(), then I do agree that it is useful
to have both it and the subtraction operator.

Thank you!


John Pruitt
Delivery Director
Doozer Software, Inc.


On Mon, Oct 12, 2015 at 10:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Haribabu Kommi <kommi.haribabu@gmail.com> writes:
> > On Tue, Oct 13, 2015 at 1:50 AM, John Pruitt <jpruitt@doozer.com> wrote:
> >> 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.
>
> > I also feel like that it is a bug. Here I attached a patch that
> > corrects the problem.
>
> I think you both are fundamentally missing the point.
>
> The age() function exists to provide a "symbolic" difference between two
> timestamps.  In its intended use-case, the difference between midnight
> on March 1 2015 and midnight on April 1 2015 is one month.  Not one month
> plus or minus an hour.
>
> If you want the other behavior, why aren't you using plain timestamp
> subtraction?
>
> regression=# select age('1 april 2015'::timestamptz, '1 march
> 2015'::timestamptz);
>   age
> -------
>  1 mon
> (1 row)
>
> regression=# select '1 april 2015'::timestamptz - '1 march
> 2015'::timestamptz;
>      ?column?
> ------------------
>  30 days 23:00:00
> (1 row)
>
> (This is based on US DST rules, you might get different results in other
> timezones.)
>
> Even discounting any thought of backwards compatibility, it does not seem
> to me that erasing the distinction between these behaviors is a good
> thing.  People might legitimately want either one.
>
> Another point worth considering is this:
>
> regression=# select '1 march 2015'::timestamptz + age('1 april
> 2015'::timestamptz, '1 march 2015'::timestamptz);
>         ?column?
> ------------------------
>  2015-04-01 00:00:00-04
> (1 row)
>
> While I'm not sure that "X + age(Y, X) = Y" holds universally, it does
> hold in this example, and the proposed patch would break that.
>
> Having said that, I notice that the seemingly even more obvious
> identity "X + (Y - X) = Y" doesn't work in this case.  Maybe we should do
> something about that, or maybe not.  My point is mainly that there are
> a *lot* of moving parts in this area, as well as a considerable amount
> of backwards-compatibility history that we must not take lightly.  It is
> well to remember also that civil time and DST laws were written by
> politicians who have never heard of mathematical consistency.
>
>                         regards, tom lane
>