pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat's wrong with my date/interval arithmetic?
4+ messages / 2 participants
[nested] [flat]
* What's wrong with my date/interval arithmetic?
@ 2004-03-02 06:01 Wojtek <wojtg@polbox.com>
2004-03-02 06:25 ` Re: What's wrong with my date/interval arithmetic? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Wojtek @ 2004-03-02 06:01 UTC (permalink / raw)
To: pgsql-sql
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','YYYY-MM-dd HH24:MI:SS')
+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')) 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"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What's wrong with my date/interval arithmetic?
2004-03-02 06:01 What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
@ 2004-03-02 06:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-02 07:00 ` Re: What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-03-02 06:25 UTC (permalink / raw)
To: Wojtek <wojtg@polbox.com>; +Cc: pgsql-sql
Wojtek <wojtg@polbox.com> writes:
> 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").
It is not "exactly the same arithmetic", because to_timestamp
delivers a result of type timestamp-with-time-zone, whereas your
other values are evidently timestamp without time zone. You did
not say what timezone setting you are using, but I think the
discrepancy is probably explained by that.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What's wrong with my date/interval arithmetic?
2004-03-02 06:01 What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
2004-03-02 06:25 ` Re: What's wrong with my date/interval arithmetic? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-02 07:00 ` Wojtek <wojtg@polbox.com>
2004-03-02 15:03 ` Re: What's wrong with my date/interval arithmetic? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Wojtek @ 2004-03-02 07:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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','YYYY-MM-dd HH24:MI:SS') as timestamp)
+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))
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','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'
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What's wrong with my date/interval arithmetic?
2004-03-02 06:01 What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
2004-03-02 06:25 ` Re: What's wrong with my date/interval arithmetic? Tom Lane <tgl@sss.pgh.pa.us>
2004-03-02 07:00 ` Re: What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
@ 2004-03-02 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-03-02 15:03 UTC (permalink / raw)
To: Wojtek <wojtg@polbox.com>; +Cc: pgsql-sql
Wojtek <wojtg@polbox.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-02 15:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-02 06:01 What's wrong with my date/interval arithmetic? Wojtek <wojtg@polbox.com>
2004-03-02 06:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-02 07:00 ` Wojtek <wojtg@polbox.com>
2004-03-02 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
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