Thread: timestamp bug
There appears to be a bug in timestamp/interval addition. It happens in
both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and
a missing day (2002 Apr 04). I discovered this by accident when I asked
the interface I'm writing for a 365 day long calendar.. Interestingly,
the missing day thing (second example) doesn't happen if only adding a few
days (like the first example). I didn't go into detail to find the point
at which it does happen.
-Cedar
////////////////////
devbarn71=# SELECT version(); version
-------------------------------------------------------------------PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC
egcs-2.91.66
(1 row)
////////////////////
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('1 days' as interval),'YYYY Mon DD'); to_char
-------------2001 Sep 06
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('2 days' as interval),'YYYY Mon DD'); to_char
-------------2001 Sep 07
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('3 days' as interval),'YYYY Mon DD'); to_char
-------------2001 Sep 07
(1 row)
devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('4 days' as interval),'YYYY Mon DD'); to_char
-------------2001 Sep 08
(1 row)
////////////////////
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('361 days' as interval),'YYYY Mon DD'); to_char
-------------2002 Apr 03
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('362 days' as interval),'YYYY Mon DD'); to_char
-------------2002 Apr 04
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('363 days' as interval),'YYYY Mon DD'); to_char
-------------2002 Apr 06
(1 row)
devbarn71=# SELECT to_char(cast('2001 apr 8' as timestamp)+cast('364 days' as interval),'YYYY Mon DD'); to_char
-------------2002 Apr 07
(1 row)
////////////////////
As an afterthought,
cedarc@nanu:~/schedule/fe$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 7
model name : Pentium III (Katmai)
stepping : 3
cpu MHz : 498.379505
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
sep_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 mmx osfxsr kni
bogomips : 496.44
Cedar Cox wrote: > > There appears to be a bug in timestamp/interval addition. It happens in > both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and > a missing day (2002 Apr 04). I discovered this by accident when I asked > the interface I'm writing for a 365 day long calendar.. Interestingly, > the missing day thing (second example) doesn't happen if only adding a few > days (like the first example). I didn't go into detail to find the point > at which it does happen. you're not hitting an issue with clocks going forward/back for summertime/daylight savings time? - Richard Huxton
Ouch. Ok, that looks like it. I guess I should just set the time part to 13:00 (when DST never changes)? Or, can I set the time zone to GMT (for this single SQL statement) or something that doesn't have DST? Perhaps a different data type? Suggestions? Thanks, -Cedar On Sun, 20 May 2001, Richard Huxton wrote: > Cedar Cox wrote: > > > > There appears to be a bug in timestamp/interval addition. It happens in > > both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and > > a missing day (2002 Apr 04). I discovered this by accident when I asked > > the interface I'm writing for a 365 day long calendar.. Interestingly, > > the missing day thing (second example) doesn't happen if only adding a few > > days (like the first example). I didn't go into detail to find the point > > at which it does happen. > > you're not hitting an issue with clocks going forward/back for > summertime/daylight savings time? > > - Richard Huxton >
Cedar Cox <cedarc@visionforisrael.com> writes:
> devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('3 days' as interval),'YYYY Mon DD');
> to_char
> -------------
> 2001 Sep 07
> (1 row)
Here in the US, the "funny" dates are in April and October:
regression=# SELECT cast('2001 oct 27' as timestamp)+cast('1 days' as interval); ?column?
------------------------2001-10-28 00:00:00-04
(1 row)
regression=# SELECT cast('2001 oct 27' as timestamp)+cast('2 days' as interval); ?column?
------------------------2001-10-28 23:00:00-05
(1 row)
Perhaps if you want only date arithmetic, not correct-to-the-second
arithmetic, you should use type date:
regression=# SELECT cast('2001 oct 27' as date) + 1; ?column?
------------2001-10-28
(1 row)
regression=# SELECT cast('2001 oct 27' as date) + 2; ?column?
------------2001-10-29
(1 row)
However, this does bring up something I've thought about before.
Type interval consists internally of two fields, months and seconds.
Intervals specified in months, years, centuries, etc convert to so many
months; everything in smaller-than-month units is converted to seconds.
Then we can do timestamp arithmetic that copes with the fact that
there's not a fixed equivalence between months and smaller units.
But, when you think about DST jumps, it's obvious that this doesn't go
far enough. There's not a fixed equivalence between days and smaller
units either.
ISTM that'2001-10-27 00:00:00-04'::timestamp + '2 days'::interval
should yield'2001-10-29 00:00:00-05'::timestamp
whereas'2001-10-27 00:00:00-04'::timestamp + '48 hours'::interval
should yield'2001-10-28 23:00:00-05'::timestamp
We cannot make that distinction now, but we could if type interval
contained three fields internally: months, days, and seconds.
regards, tom lane
Cedar Cox wrote: > > There appears to be a bug in timestamp/interval addition. It happens in > both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and > a missing day (2002 Apr 04). I discovered this by accident when I asked > the interface I'm writing for a 365 day long calendar.. Interestingly, > the missing day thing (second example) doesn't happen if only adding a few > days (like the first example). I didn't go into detail to find the point > at which it does happen. IMN1=# SELECT version(); version ---------------------------------------------------------------PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3 (1 row) And all right work. Try new version.