1

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.

mechanikos
  • 771
  • 12
  • 32

1 Answers1

2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks, and sorry for other noob questions. can i use this construction in arguments of some postgresql function? and is there any way do it without partition of my input string? – mechanikos Aug 11 '14 at 10:35
  • 2
    @mechanikos: I would export UTC timestamps in ISO 8601 format from Oracle to avoid confusion. Or UNIX epochs. I added some more. – Erwin Brandstetter Aug 11 '14 at 11:27