Rectifying wrong Date outputs - Mailing list pgsql-hackers
| From | Piyush Newe |
|---|---|
| Subject | Rectifying wrong Date outputs |
| Date | |
| Msg-id | AANLkTinveA_yn5RJQofpJjBrq3mEydK6jvFbWbGphso=@mail.gmail.com Whole thread Raw |
| Responses |
Re: Rectifying wrong Date outputs
Re: Rectifying wrong Date outputs |
| List | pgsql-hackers |
Hi,
I was randomly testing some date related stuff on PG & observed that the outputs were wrong.
e.g.
postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY');
to_date
------------
3910-01-01 <--------- Look at this
(1 row)
postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YYYY');
to_date
------------
2010-01-01
(1 row)
User can provide the year in any format i.e. 1-digit to 4-digit, in this case the internal representation of century should be logically correct.
Considering this situation, I am drawing the table below & logging my observation here. This might help us to demonstrate where we are lacking in PG.
Data Format PostgreSQL EDBAS
TO_DATE('01-jan-1', 'DD-MON-Y') 2001-01-01 01-JAN-2001
TO_DATE('01-jan-1', 'DD-MON-YY') 2001-01-01 01-JAN-2001
TO_DATE('01-jan-1', 'DD-MON-YYY') 2001-01-01 01-JAN-2001
TO_DATE('01-jan-1', 'DD-MON-YYYY') 0001-01-01 01-JAN-0001
In this case, all the cases seems correct. Also the YEAR part in the output is seems logical.
Data Format PostgreSQL EDBAS
TO_DATE('01-jan-10', 'DD-MON-Y') 2010-01-01 Error
TO_DATE('01-jan-10', 'DD-MON-YY') 2010-01-01 01-JAN-2010
TO_DATE('01-jan-10', 'DD-MON-YYY') 2010-01-01 01-JAN-2010
TO_DATE('01-jan-10', 'DD-MON-YYYY') 0010-01-01 01-JAN-0010
In this case, it seems in last 3 cases PG is behaving correctly. Whereas in 1st case the output is not correct since the Format ('Y') is lesser than the actual input ('10'). But PG is ignoring this condition and throwing whatever is input. The output year is might not be the year, what user is expecting. Hence PG should throw an error.
Data Format PostgreSQL EDBAS
TO_DATE('01-jan-067', 'DD-MON-Y') 2067-01-01 Error
TO_DATE('01-jan-111', 'DD-MON-YY') 2011-01-01 Error
TO_DATE('01-jan-678', 'DD-MON-YYY') 1678-01-01 01-JAN-2678
TO_DATE('01-jan-001', 'DD-MON-YYYY') 0001-01-01 01-JAN-0001
In this case, only last case seems correct in PG. Rest other cases are might not be logical, rather the output is vague. In PG, I haven't seen any document which is saying something like this, if year is 111...999 then the century would be 2000 and 001...099 then then century would be 1000. However, the 1st and 2nd case should throw an error since the output format ('Y' & 'YY') are really not matching with the Input ('067' & '111'), respectively.
Data Format PostgreSQL EDBAS
TO_DATE('01-jan-2010', 'DD-MON-Y') 4010-01-01 Error
TO_DATE('01-jan-2010', 'DD-MON-YY') 3910-01-01 Error
TO_DATE('01-jan-2010', 'DD-MON-YYY') 3010-01-01 Error
TO_DATE('01-jan-2010', 'DD-MON-YYYY') 2010-01-01 01-JAN-2010
In this case, PG is giving wrong output in first 3 cases. Those need to get rectified. Again it should throw error in these cases, because Output Format is not matching with Input Data. The exception here is the 2nd case, where century is well-defined.
After observing the all above cases, the summary would be, if the output format is lesser than the actual input value, then it should throw an error. Considering this thumb rule, we can fix the wrong outputs in PG. I have made necessary changes to the code & attaching the patch with this email.
In the patch, I have written one centralize function which will decide the century depends upon the given Input format.
Thoughts ? Any other ideas on this ?
--
Piyush S Newe
Principal Engineer
EnterpriseDB
office: +91 20 3058 9500
www.enterprisedb.com
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Attachment
pgsql-hackers by date: