Thread: to_timestamp behaviour

Marcel Ruff <mr@marcelruff.info> writes:
> I do this ISO date query:

>   select to_timestamp('2012-07-06T23:17:39.668Z', 'YYYY-MM-DD\THH24:MI:SS.MS'),
> '2012-07-06T23:17:39.668Z' AS ORIGINAL;

>          to_timestamp        |         original
> ----------------------------+--------------------------
>  2012-07-06 03:17:39.668+00 | 2012-07-06T23:17:39.668Z
> (1 row)

I see nothing in the to_timestamp documentation suggesting that
backslash is how to quote constant text.  Try it like this:

select to_timestamp('2012-07-06T23:17:39.668Z', 'YYYY-MM-DD"T"HH24:MI:SS.MS');

            regards, tom lane

On 01/29/2013 05:23 PM, Tom Lane wrote:
> Marcel Ruff <mr@marcelruff.info> writes:
>> I do this ISO date query:
>>    select to_timestamp('2012-07-06T23:17:39.668Z', 'YYYY-MM-DD\THH24:MI:SS.MS'),
>> '2012-07-06T23:17:39.668Z' AS ORIGINAL;
>>           to_timestamp        |         original
>> ----------------------------+--------------------------
>>   2012-07-06 03:17:39.668+00 | 2012-07-06T23:17:39.668Z
>> (1 row)
> I see nothing in the to_timestamp documentation suggesting that
> backslash is how to quote constant text.  Try it like this:
>
> select to_timestamp('2012-07-06T23:17:39.668Z', 'YYYY-MM-DD"T"HH24:MI:SS.MS');
>
>             regards, tom lane
>
>
I am not sure that is the OP's full issue. By "ISO date" I assume he
means a format among those specified in ISO8601. Since the input
specifies "Z" as the time-zone-designator it's likely that he is looking
for the result that simple casting will yield:

steve@[local] => select '2012-07-06T23:17:39.668Z'::timestamptz;
         timestamptz
----------------------------
  2012-07-06 16:17:39.668-07

The to_timestamp templates given do not include a time-zone-designator
so it is interpreted as local time which I don't think is what was intended.

select to_timestamp('2012-07-06T23:17:39.668Z',
'YYYY-MM-DD"T"HH24:MI:SS.MS');
to_timestamp
----------------------------
2012-07-06 23:17:39.668-07

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.

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.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