Sorry, but i`m noob and i need your advices. I have some result set from oracle with timestamptz, i get string value of this timestamptz which like
2014-1-10 13.47.56.0 7:0
and then I need put it in postgresql-request in some function which takes timestamp with timezone. How can I convert this this string to timestamp with timezone in java? I tried do something look like
TO_TIMESTAMP_TZ("2014-1-10 13.47.32.0 7:0","YYYY-MM-DD HH24:MI:SS.FF TZH:TZM") but it didn`t work for me. Help me, please.
Advertisement
Answer
I don’t know of a function TO_TIMESTAMP_TZ() in Postgres. You probably mean to_timestamp().
I also don’t know of a template patterns TZH:TZM. You could use the AT TIME ZONE construct.
And data is quoted with single quotes; double quotes are for identifiers.
This works for me:
SELECT to_timestamp('2014-1-10 13.47.32.0', 'YYYY-MM-DD HH24:MI:SS.MS')::timestamp
AT TIME ZONE '-7:0'; -- shift back
It would be smarter to output timestamps in ISO 8601 format with TZR (time zone region) in Oracle, which is less ambiguous. Details in the Oracle manual.
Or better yet, UTC timestamps (without 0 offset), which you can cast to timestamptz directly in Postgres:
SELECT '2014-01-10 06:47:32+0'::timestamptz;
Or UNIX epochs, which can be fed to the second form of to_timestamp().
SELECT to_timestamp(1389336452);
Details about timestamps in Postgres: