Обсуждение: bizarre AGE behaviour

Hello all,
Many thanks for this fine product.
We began encountering some unexpected date related errors this week and
after further investigation found the following. We use the postgres AGE
function in a custom function. The AGE function has begun to throw some
unanticipated results back. This has not happened before and my first
guess is that it may be leap year related. Here are some examples.

This is good...
network=# select age('04-01-04','03-01-04');
  age
-------
 1 mon
(1 row)

This isn't...
network=# select age('05-01-04','03-01-04');
         age
---------------------
 1 mon 30 days 23:00
(1 row)

Now it gets really strange......
network=# select age('06-01-04','04-01-04');
         age
---------------------
 1 mon 29 days 23:00
(1 row)

This may have been addressed in the past (I subscribe to this list) and
I just missed it, but is there a simple fix for this problem?
Thanks.
--
Bill MacArthur
Webmaster
The DHS Club, Inc.
The Best Is Yet To Come!

On Wednesday 03 March 2004 9:19 am, DHS Webmaster wrote:
> We began encountering some unexpected date related errors this
week...
<snip>
> This is good...
> network=# select age('04-01-04','03-01-04');
>   age
> -------
>  1 mon
> (1 row)
>
> This isn't...
> network=# select age('05-01-04','03-01-04');
>          age
> ---------------------
>  1 mon 30 days 23:00
> (1 row)
>
> Now it gets really strange......
> network=# select age('06-01-04','04-01-04');
>          age
> ---------------------
>  1 mon 29 days 23:00
> (1 row)
>
> This may have been addressed in the past (I subscribe to this list)
> and I just missed it, but is there a simple fix for this problem?
> Thanks.

It was originally addressed long in the past (1784, Paris by Benjamin
Franklin): http://webexhibits.org/daylightsaving/

US Daylight Saving Time starts this year on April 4 when 0200 jumps to
0300. The answers PostgreSQL gave are correct.

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.com> writes:
> US Daylight Saving Time starts this year on April 4 when 0200 jumps to
> 0300. The answers PostgreSQL gave are correct.

I suspect what the OP wants is non-timezone-aware behavior, which he
could get by casting the inputs of age() to timestamp without time
zone.  As written the system is preferring to interpret them as
timestamp with time zone.

Also, if what's really wanted is just resolution to the day level,
the date subtraction operator might be a lot better choice than
age() anyway.

regression=# select '06-01-04'::date - '04-01-04'::date;
 ?column?
----------
       61
(1 row)


            regards, tom lane

On Wednesday 03 March 2004 3:19 pm, Tom Lane wrote:
> Steve Crawford <scrawford@pinpointresearch.com> writes:
> > US Daylight Saving Time starts this year on April 4 when 0200
> > jumps to 0300. The answers PostgreSQL gave are correct.
>
> I suspect what the OP wants is non-timezone-aware behavior...

You are probably right. In his original post he posited that the
problem was due to some sort of leap-year bug in PostgreSQL. I was
just pointing him to the real cause of his observations so he could
tackle whatever problem he was having secure in the knowledge that PG
was working correctly.

Cheers,
Steve

You are both right.
Your explanation, Steve, was the light that got me going on a simpler
solution along the lines of what Tom suggested. I didn't really need
AGE, and upon digging in, couldn't even remember why I had chosen that
in the first place.
Postgres is the bomb!
Thanks guys.

Steve Crawford wrote:
>
> On Wednesday 03 March 2004 3:19 pm, Tom Lane wrote:
> > Steve Crawford <scrawford@pinpointresearch.com> writes:
> > > US Daylight Saving Time starts this year on April 4 when 0200
> > > jumps to 0300. The answers PostgreSQL gave are correct.
> >
> > I suspect what the OP wants is non-timezone-aware behavior...
>
> You are probably right. In his original post he posited that the
> problem was due to some sort of leap-year bug in PostgreSQL. I was
> just pointing him to the real cause of his observations so he could
> tackle whatever problem he was having secure in the knowledge that PG
> was working correctly.
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

--
Bill MacArthur
Webmaster
The DHS Club, Inc.
The Best Is Yet To Come!