to_timestamp behaviour

[Marcel Ruff]

Hi,

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)


the result for the hour is "03" instead of the expected "23"!


Trying without the valid 'T' as separator:

watchee=# select to_timestamp('2012-07-06T23:17:39.668', 'YYYY-MM-DD HH24:MI:SS.MSZ'), '2012-07-06T23:17:39.668Z' AS ORIGINAL;
        to_timestamp        |         original        
----------------------------+--------------------------
 2012-07-06 23:17:39.668+00 | 2012-07-06T23:17:39.668Z
(1 row)

it works fine.

Shouldn't the first variant be OK as well?

Thanks
Marcel

[Tom Lane]

Marcel Ruff <m...@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


--
Sent via pgsql-novice mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Steve Crawford]

On 01/29/2013 05:23 PM, Tom Lane wrote:
> Marcel Ruff <m...@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

[Tom Lane]

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