Postgres Pro
Downloads
Home   >   mailing lists

Thread: to_timestamp() too loose?

to_timestamp() too loose?

From
Magnus Hagander
Date:
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



Should we really convert 00 to 01?

We also do things like:
postgres=# select to_timestamp('2012-00-99', 'yyyy-mm-dd');     to_timestamp
------------------------2012-04-08 00:00:00+02


And while I guess there's some logic in that, it's not documented
AFAICT. Or am I just not finding the docs?

-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

Re: to_timestamp() too loose?

From
Amit Kapila
Date:
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@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.

Re: to_timestamp() too loose?

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> 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

> Should we really convert 00 to 01?

to_timestamp is intentionally pretty loose.  Personally, if I wanted
sanity checking on a date string in any common format, I would just
cast the string to timestamp(tz), and *not* use to_timestamp.
        regards, tom lane

Re: to_timestamp() too loose?

From
Magnus Hagander
Date:
On Thu, Aug 23, 2012 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> 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
>
>> Should we really convert 00 to 01?
>
> to_timestamp is intentionally pretty loose.  Personally, if I wanted
> sanity checking on a date string in any common format, I would just
> cast the string to timestamp(tz), and *not* use to_timestamp.

Shouldn't we put at least a note, and IMO even a *warning* in the docs
saying that it is like this? (or am I missing one we have) It's not
really consistent with how most of postgres works :)

-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

Re: to_timestamp() too loose?

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Aug 23, 2012 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> to_timestamp is intentionally pretty loose.  Personally, if I wanted
>> sanity checking on a date string in any common format, I would just
>> cast the string to timestamp(tz), and *not* use to_timestamp.

> Shouldn't we put at least a note, and IMO even a *warning* in the docs
> saying that it is like this? (or am I missing one we have) It's not
> really consistent with how most of postgres works :)

I have no objection to a note and even a couple of examples, but
try to keep down the dudgeon --- the reason it's like this is that
people have found it useful for the conversion to be forgiving about
field ranges.  An example is that you can compute "next week" by
adding 7 to the day field, without worrying about whether you need
to wrap that at 28, 29, 30, or 31 days.  This behavior corresponds
directly to Unix mktime(3), which is required by POSIX spec to be
lax about field ranges.
        regards, tom lane