timestamp bug

From: Cedar Cox <cedarc(at)visionforisrael(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: timestamp bug
Date: 2001-05-20 09:49:00
Message-ID: Pine.LNX.4.21.0105201225150.17216-100000@nanu.visionforisrael.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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(at)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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Cedar Cox 2001-05-20 10:07:25 Re: Calculating the age of a person
Previous Message Joe Conway 2001-05-20 01:26:07 Re: Truncation of char, varchar, bit, varbit types (fwd)