Re: to_timestamp() too loose?

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)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.