Re: date comparisons - Mailing list pgsql-general

From Belinda M. Giardine
Subject Re: date comparisons
Date
Msg-id Pine.GSO.4.53.0612121235540.28737@galapagos.bx.psu.edu
Whole thread Raw
In response to Re: date comparisons  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general

On Tue, 12 Dec 2006, Tom Lane wrote:

> "Belinda M. Giardine" <giardine@bio.cse.psu.edu> writes:
> > Should it be this way?
>
> Well, to_timestamp() is apparently designed not to complain when the
> input doesn't match the format, which is not my idea of good behavior
> ... but your example is in fact wrong.  'Month' means a 9-character
> field, so you are short a couple of spaces.
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
>       to_timestamp
> ------------------------
>  0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January   2006', 'Month YYYY');
>       to_timestamp
> ------------------------
>  2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
>       to_timestamp
> ------------------------
>  2006-01-01 00:00:00-05
> (1 row)

Thanks.  I wanted to understand the reason for my attempt not working no
matter which method I used in the end.  Help to prevent future errors.

>
> Or, as suggested upthread, forget to_timestamp and just use the native
> timestamp or date input conversion, which on the whole is a lot more
> robust (it *will* throw an error if it can't make sense of the input,
> unlike to_timestamp).
>
>             regards, tom lane

Good to know.
Belinda


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Why DISTINCT ... DESC is slow?
Next
From: Ron Johnson
Date:
Subject: Re: Why DISTINCT ... DESC is slow?