pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
bizarre AGE behaviour
5+ messages / 3 participants
[nested] [flat]

* bizarre AGE behaviour
@ 2004-03-03 17:19 DHS Webmaster <webmaster@dhs-club.com>
  2004-03-03 18:00 ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
  0 siblings, 1 reply; 5+ messages in thread

From: DHS Webmaster @ 2004-03-03 17:19 UTC (permalink / raw)
  To: pgsql-admin

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!



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: bizarre AGE behaviour
  2004-03-03 17:19 bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
@ 2004-03-03 18:00 ` Steve Crawford <scrawford@pinpointresearch.com>
  2004-03-03 23:19   ` Re: bizarre AGE behaviour Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Steve Crawford @ 2004-03-03 18:00 UTC (permalink / raw)
  To: DHS Webmaster <webmaster@dhs-club.com>; pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: bizarre AGE behaviour
  2004-03-03 17:19 bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
  2004-03-03 18:00 ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
@ 2004-03-03 23:19   ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-04 16:57     ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2004-03-03 23:19 UTC (permalink / raw)
  To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: DHS Webmaster <webmaster@dhs-club.com>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: bizarre AGE behaviour
  2004-03-03 17:19 bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
  2004-03-03 18:00 ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
  2004-03-03 23:19   ` Re: bizarre AGE behaviour Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-04 16:57     ` Steve Crawford <scrawford@pinpointresearch.com>
  2004-03-04 18:40       ` Re: bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Steve Crawford @ 2004-03-04 16:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: DHS Webmaster <webmaster@dhs-club.com>; pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: bizarre AGE behaviour
  2004-03-03 17:19 bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
  2004-03-03 18:00 ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
  2004-03-03 23:19   ` Re: bizarre AGE behaviour Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-04 16:57     ` Re: bizarre AGE behaviour Steve Crawford <scrawford@pinpointresearch.com>
@ 2004-03-04 18:40       ` DHS Webmaster <webmaster@dhs-club.com>
  0 siblings, 0 replies; 5+ messages in thread

From: DHS Webmaster @ 2004-03-04 18:40 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

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!



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-03-04 18:40 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-03 17:19 bizarre AGE behaviour DHS Webmaster <webmaster@dhs-club.com>
2004-03-03 18:00 ` Steve Crawford <scrawford@pinpointresearch.com>
2004-03-03 23:19   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 16:57     ` Steve Crawford <scrawford@pinpointresearch.com>
2004-03-04 18:40       ` DHS Webmaster <webmaster@dhs-club.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox