Hi guys,
Firstly,
Postgres rocks! Our live database is rapidly growing and
ever-reliable. We're stupidly storing URLs visited by nearly a thousand
of our own machines, and are yet to miss a beat (apart from the obvious
speed problems)!
Secondly, we have a problem with interval math, detailed below. Should
be reproducible on any platform(?)
Regards,
- Mitchell Brandsma (mitchell@pienetworks.com)
Version: 7.0.2 (is it fixed in the latest?)
To reproduce:
psql <nameyourdatabase>
=# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
?column?
----------------------
1 year 360 00:00 ago ### Wrong! (Should be 5 or 6 days depending
on interpretation?)
(1 row)
=# select '2 years ago'::interval - '1 year 360 days 00:00'::interval;
?column?
-----------------------
3 years 360 00:00 ago ### Correct
=# select '365 days'::interval - '1 year'::interval; <-- should
be ~ 0 or 1 day(s)
?column?
----------------------
1 year 365 00:00 ago ### Wrong!
(1 row)
Another eg:
=# select 'today'::datetime;
?column?
------------------------
2001-01-10 00:00:00+08 ### Today
(1 row)
=# create temp table testdate_mkb (td interval);
CREATE
=# insert into testdate_mkb select ('2 years'::interval - '1 year 353
00:00 ago') ;
INSERT 246954387 1
=# select * from testdate_mkb;
td
-------------------
3 years 353 00:00 ### !!!!!!
(1 row)
pielive=# select 'today'::datetime + td from testdate_mkb;
?column?
------------------------
2004-12-28 00:00:00+08
(1 row)
The correct answer is of course about 12 days (2 years - 1 year 353
days) from now, not nearly 4 years
Platforms tried on:
PIII 500, uname: Linux dbserver 2.2.13 #21 Thu Dec 16 06:50:30 WST 1999
i686 unknown
Celeron, uname: Linux dbserver 2.2.12-20 #1 Mon Sep 27 10:40:35 EDT 1999
i686 unknown (RedHat)