From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander Sent: Thursday, August 23, 2012 2:08 PM
> postgres=# select to_timestamp('2012-08-01', 'yyyy-mm-dd'); > to_timestamp > ------------------------ > 2012-08-01 00:00:00+02 > postgres=# select to_timestamp('2012-08-00', 'yyyy-mm-dd'); > to_timestamp > ------------------------ > 2012-08-01 00:00:00+02 > postgres=# select to_timestamp('2012-00-00', 'yyyy-mm-dd'); > to_timestamp > ------------------------ > 2012-01-01 00:00:00+01 For the above different databases have different behaviour Oracle - return error for 2 and 3 stating invalid day, invalid month respectively. MySQL - return output as follows select to_timestamp('2012-08-00', 'yyyy-mm-dd'); 2012-07-31 00:00:00 select to_timestamp('2012-00-00', 'yyyy-mm-dd'); 2011-11-30 00:00:00 > Should we really convert 00 to 01? I believe for invalid dates, behavior is database dependent, so the behavior of PG should be okay. > We also do things like: > postgres=# select to_timestamp('2012-00-99', 'yyyy-mm-dd'); > to_timestamp > ------------------------ > 2012-04-08 00:00:00+02 For the above different databases have different behaviour Oracle - returns error stating invalid month. MySQL - NULL PG - as it converts to julian date, so the output is based on that calculation. In this, it should actually throw error because user might not be able to makeout any relation of output. However that will create behavior inconsistency. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers