bizarre AGE behaviour

[DHS Webmaster]

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.

[Steve Crawford]

Post by DHS Webmaster
We began encountering some unexpected date related errors this

week...
<snip>

Post by DHS Webmaster
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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Steve Crawford
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

---------------------------(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

[Steve Crawford]

Post by Steve Crawford
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

[DHS Webmaster]

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.

Post by Steve Crawford

Post by Steve Crawford
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!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly