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]

Post by Marcel Ruff
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

[Steve Crawford]

Post by Tom Lane

Post by Marcel Ruff
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
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]

Post by Steve Crawford
However my attempt to include the time zone in the template (based on
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