From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug #761: Date format ambiguity while insertion!!!! |
Date: | 2002-09-09 08:48:29 |
Message-ID: | 20020909084829.83B06475BD7@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Shubhankar Dasgupta (dasgupta(at)intelesoftech(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Date format ambiguity while insertion!!!!
Long Description
I am using Postgres 7.2.2 on Redhat Linux 7.2
My datestyle settings have been set as "ISO US(non european)"
This would mean that my date values should get inserted in the mm-dd-yyyy format. "09-01-2002" is interpreted as Sepetember 1st, 2002 while "01-09-2002" is interpreted as January 9th, 2002.
This works perfectly most of the time except during certain cases where my month field exceeds 12.
As an example--consider "13-01-2002". As per my datesyle settings, postgres should have tried to interpret this as the 1st day of some "UNKNOWN" month of 2002 and should have thrown an error. However this date gets inserted and gets stored as 13th January, 2002 (interperted in the dd-mm-yyyy format).
Its only when both the first two fields exceed 12(something like 13-13-2002), that I am given an error "Bad date external representation '13-13-2002'. If either of the first two fields is less than or equal to 12, postgres accepts that as the month field irrespective of my datestyle settings.
This may be a flexibility feature but then this I believe is extremely dangerous as it would accept user-errors without the user knowing it.
Sample Code
ims=> insert into testdate values('1','09-01-2002');
INSERT 1590456 1
ims=> insert into testdate values('2','01-09-2002');
INSERT 1590457 1
ims=> insert into testdate values('3','13-09-2002');
INSERT 1590458 1
ims=> insert into testdate values('4','09-13-2002');
INSERT 1590459 1
ims=> insert into testdate values('5','13-13-2002');
ERROR: Bad date external representation '13-13-2002'
ims=> select * from testdate ;
dateid | mydate
--------+------------
1 | 2002-09-01
2 | 2002-01-09
3 | 2002-09-13
4 | 2002-09-13
(4 rows)
No file was uploaded with this report
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Gertsog | 2002-09-09 09:16:13 | update 7.0 -> 7.2.2 problem with shared libraries?! |
Previous Message | Joe Conway | 2002-09-09 05:31:26 | Re: Schemas not available for pl/pgsql %TYPE.... |