Обсуждение: 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!