[SQL] What's wrong with my date/interval arithmetic?
Hi,
I ran this query on Postgres 7.3:
select min_time, max_time, min_time+age(max_time,min_time) as result,
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS')
+age(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS'),
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS')) as expected_result
from
(select min(postingblogdate) as min_time from blg_weblog_header) as min_time,
(select max(postingblogdate) as max_time from blg_weblog_header) as max_time
and get a result like this:
min_time,max_time,result,expected_result
2003-10-17 23:07:00,2003-12-01 03:50:45,2003-12-02 03:50:45,2003-12-01 03:50:45
Why is the "result" incorrect (off by one day)?
When I do the exactly same arithmetic using timestamps created
with to_timestamp, everything is OK ("expected_result").
Is this a bug or am I doing something worng?
thanks for any thoughts about this problem,
Wojtek
P.S. postingblogdate is of type "timestamp without time zone"
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] What's wrong with my date/interval arithmetic?
TL> It is not "exactly the same arithmetic", because to_timestamp
TL> delivers a result of type timestamp-with-time-zone, whereas your
TL> other values are evidently timestamp without time zone. You did
TL> not say what timezone setting you are using, but I think the
TL> discrepancy is probably explained by that.
I tried doing this: (casting to 'timestamp without timezone')
select cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as timestamp)
+age(cast(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS') as
timestamp),
cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as
timestamp))
And it turns out you were right Tom, the result is
2003-12-02 03:50:45, so the data type _does_ matter.
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','-MM-dd HH24:MI:SS') as
timestamp),
cast(to_timestamp('2003-10-17 23:07:00','-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','-MM-dd HH24:MI:SS'),
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS'))
is '1 mon 13 days 04:43:45'
But it still doesn't answer the questions why there is a difference in
results and why it is exactly one day.
The "timestamp without time zone" arithmetic should produce correct
results when all operations are done _within_ that data type (without
mixing types), yet I'm getting this one day discrepancy.
puzzled Wojtek
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
