Re: What's wrong with my date/interval arithmetic?

Wojtek <wojtg(at)polbox(dot)com> writes:
> Investigating that a little further I found out that there is a difference
> in results returned by age:

> select age(cast(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS') as timestamp),
> cast(to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp))
> is '1 mon 14 days 04:43:45'

> and

> select age(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'),
> to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS'))
> is '1 mon 13 days 04:43:45'

I get '1 mon 14 days 04:43:45' and '1 mon 14 days 05:43:45'
respectively. This is a reasonable result for my timezone (EST5EDT),
because there is a daylight-savings transition involved:

regression=# select to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS');
to_timestamp
------------------------
2003-12-01 03:50:45-05
(1 row)

regression=# select to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS');
to_timestamp
------------------------
2003-10-17 23:07:00-04
(1 row)

Note the October date is taken as GMT-4, the December GMT-5. The hour
gained in the fall DST transition is accounted for when doing
timezone-aware arithmetic, but not when doing timezone-free arithmetic.

I still think the behavior you see is related to the timezone you're
using, which you still haven't told us. Also, what PG version are you
running, and on what platform?

regards, tom lane