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
