Re: to_timestamp behaviour

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