Re: to_timestamp behaviour

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> However my attempt to include the time zone in the template (based on
> minimal futzing) have failed:
> select to_timestamp('2012-07-06T23:17:39.668Z',
> 'YYYY-MM-DD"T"HH24:MI:SS.MSTZ');
> ERROR: "TZ"/"tz" format patterns are not supported in to_date

> ?!?

> So I think that casting will solve the OP's issue but I'm puzzled as to
> why I have been unable to get to_timestamp to recognize an ISO8601 input
> format.

I think the reason why nobody's bothered to make that work is that
timezone specs come in such a huge variety of flavors ('-05', 'EST',
'EST5EDT', 'America/New_York' being just a few of the possibilities
that apply where I live) that it's a bit silly to try to handle them
in to_timestamp, which is really only intended to handle narrowly
defined *and nonstandard* input formats.

If the standard input converter for datetimes will handle the format
you're trying to cope with (which it most assuredly will for ISO
format, and 99% of the time for other sensible formats too), then
why would you bother with to_timestamp? It's just a nuisance.

regards, tom lane