Re: is this my date problem

Theodore Petrosky <tedpet5(at)yahoo(dot)com> writes:
> I should have sent this... it is very interesting..
> agencysacks=# select to_timestamp('01 October 2003
> 00:01', 'DD Month YYYY HH24:MI');
> to_timestamp
> ---------------------
> 0003-10-01 00:01:00
> (1 row)

Oh ... duh ... you should have said

regression=# select to_timestamp('01 October 2003 00:01', 'DD FMMonth YYYY HH24:MI');
to_timestamp
------------------------
2003-10-01 00:01:00-04
(1 row)

There's been repeated discussion about whether our to_timestamp code
should be more forgiving of input that does not match the given format,
but right at the moment it's pretty unforgiving.

BTW, have you considered the likelihood that you shouldn't be using
to_timestamp at all? The timestamp datatype input converter gets this
right:

regression=# select '01 October 2003 00:01'::timestamp;
timestamp
---------------------
2003-10-01 00:01:00
(1 row)

ISTM that to_timestamp is intended for cases where you want to be rigid
about the data format. If you think that the input data is
self-explanatory then try just casting it to timestamp.

regards, tom lane