0

We are working on spring boot hibernate application that deals with databases. We backup the database tables into csv format. While backup Oracle 11g table that contains TIMESTAMP WITH TIME ZONE columns we are facing issues as this type can have two types in their values:

  • Time zone offset -> 2003-01-01 02:00:00.0 -7:00
  • Time zone region name -> 2000-02-29 03:32:22.016 America/Yellowknife

We are using apache metamodel to parse CSV to restore into another Oracle database table.

Exported timestamp column in CSV :

"Col_TimeStampTimeZone" 
"2016-04-07 08:42:20.828 Asia/Calcutta" 
"2003-01-01 02:00:00.0 -7:00"

We have tried many ways to convert using SimpleDateFormat, Date, Calendar but we are not able to restore exactly as it is saved in source database table.

We have tried solution given here : Convert `Java.lang.String` TO `oracle.sql.TIMESTAMPTZ`

This code working fine on Windows OS. It converting according to local time zone in time zone region format. But same code not working on Ubuntu OS. It converting value in UTC as :

07-APR-16 03.12.20.828000000 AM UTC
01-JAN-03 09.00.00.000000000 AM UTC

But I want them to restore as time stamp with time zone region or time zone offset (any one but same format).

We dont want to loose and save erroneous date into restored database table, need exactly what it was into source table.

Community
  • 1
  • 1
Neelam Sharma
  • 2,745
  • 4
  • 32
  • 73
  • And what is the problem? You cannot parse both types or only the second one? Or you want it without a time zone in UTC time? How you export to CSV. Are you using to_char. If it is so, use format string with TZH:TZM inside to get only the first type. Or if you want in UTC zone use sys_extract_utc to get it in timestamp without time zone, – Mottor Jul 08 '16 at 14:17
  • I want java code to convert value of time stamp to restore in timestamp format either with region format or with offset format. – Neelam Sharma Jul 11 '16 at 06:14

0 Answers0