Convert `Java.lang.String` TO `oracle.sql.TIMESTAMPTZ`

Question

I have these following Java.lang.String values that represents String value of TIMESTAMPTZ. I need to convert these Java.lang.String TO oracle.sql.TIMESTAMPTZ.

"2016-04-19 17:34:43.781 Asia/Calcutta",
"2016-04-30 20:05:02.002 8:00",
"2003-11-11 00:22:15.0 -7:00",
"2003-01-01 02:00:00.0 -7:00",
"2007-06-08 15:01:12.288 Asia/Bahrain",
"2016-03-08 17:17:35.301 Asia/Calcutta",
"1994-11-24 11:57:17.303"

I tried it by many ways.

Sample 1:

Tried it by using SimpleDateFormat

String[] timeZoneValues = new String[]{"2016-04-19 17:34:43.781 Asia/Calcutta", "2016-04-30 20:05:02.002 8:00", "2003-11-11 00:22:15.0 -7:00", "2003-01-01 02:00:00.0 -7:00", "2007-06-08 15:01:12.288 Asia/Bahrain", "2016-03-08 17:17:35.301 Asia/Calcutta", "1994-11-24 11:57:17.303"};
        for(String timeZoneValue: timeZoneValues){
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS XXX");
            try {
                simpleDateFormat.parse(timeZoneValue);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

That thrown an Exception:

java.text.ParseException: Unparseable date: "2016-04-19 17:34:43.781 Asia/Calcutta"
    at java.text.DateFormat.parse(DateFormat.java:357)

Sample 2:

Tried it by converting these String values directly into Timestamp or oracle.sql.TIMESTAMPTZ

String parse = "2016-04-19 17:34:43.781 8:00";
        try {
            Timestamp timestamp = Timestamp.valueOf("2016-04-19 17:34:43.781 8:00");
        }catch (Exception ex){
            ex.printStackTrace();
        }

Exception:

java.lang.NumberFormatException: For input string: "781 8:000"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Integer.parseInt(Integer.java:492)
    at java.lang.Integer.parseInt(Integer.java:527)
    at java.sql.Timestamp.valueOf(Timestamp.java:253)

Sample 3:

String parse = "2016-04-19 17:34:43.781 Asia/Calcutta";
DateTimeFormatter dateTimeFormatter = ISODateTimeFormat.dateTimeNoMillis();
DateTime dateTime = dateTimeFormatter.parseDateTime(parse);
Timestamp timeStamp = new Timestamp(dateTime.getMillis());

Exception:

Invalid format: "2016-04-19 17:34:43.781 Asia/Calcutta" is malformed at " 17:34:43.781 Asia/Calcutta"

Sample 4:

try {
TIMESTAMPTZ timestamptz = new TIMESTAMPTZ(connection, (String) colValue);
}catch (Exception ex){
ex.printStackTrace();
}

Exception:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
    at java.sql.Timestamp.valueOf(Timestamp.java:249)
    at oracle.sql.TIMESTAMPTZ.toBytes(TIMESTAMPTZ.java:1919)
    at oracle.sql.TIMESTAMPTZ.<init>(TIMESTAMPTZ.java:253)

I am trying to insert the TIMESTAMPTZ value into Oracle database using Apache Metamodel and I have Java 1.7 installed on my system.

You don't have a consistent format of dates in the inputs. That will be pretty hard to handle the conversion. — Mubin, Apr 28 '16 at 10:48
@Mubin Can you please suggest all possible solutions even strings are not consistent? — Ashish Pancholi, May 03 '16 at 12:25
I'm afraid you might need to create your own implementation of `DateFormat` — user902383, May 03 '16 at 13:09
With such inconsistent formats there is nothing that you can use out-of-the-box for conversion. You will have to write your own formatter/converter. — Mubin, May 03 '16 at 14:05
Interesting question, the offset part "8:00" is the real challenge here. I have opened an [issue](https://github.com/MenoData/Time4J/issues/502) in my time library Time4J to track this problem. Ideas or proposals how to handle it are welcome. — Meno Hochschild, May 07 '16 at 22:20

score 2 · Accepted Answer · answered May 04 '16 at 13:46

Your timestamps are not in a standard java parseable formats. So in order to parse them you need to write custom code for handling such formats.

Couple of observations:

Asia/Calcutta is not a valid Parseable TimeZone, hence you need some mechanism to get corresponding timezone.

8:00 is also not a valid Parseable Timezone in java, hence you need some mechanism to format it in a valid value +08:00

Keeping above points in mind, following code will do the needful for you.

    SimpleDateFormat dateFormatTZGeneral = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
    SimpleDateFormat dateFormatTZISO = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS XXX");
    SimpleDateFormat dateFormatWithoutTZ = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");


    String[][] zoneStrings = DateFormatSymbols.getInstance().getZoneStrings();

    Date date = null;

    String[] timeStampSplits = timestamp.split(" ");
    if(timeStampSplits.length>2) {

        String timezone = timeStampSplits[2];
        //First Case Asia/Calcutta
        if(Character.isAlphabetic(timezone.charAt(timezone.length()-1))) {

            for(String[] zoneString: zoneStrings) {
                if(zoneString[0].equalsIgnoreCase(timezone)) {
                    timeStampSplits[2] = zoneString[2];
                    break;
                }
            }

            timestamp = createString(timeStampSplits," ");
            date = getDate(timestamp, dateFormatTZGeneral);
        } else {
            //Second Case 8:00
            timeStampSplits[2] = formatTimeZone(timeStampSplits[2]);

            timestamp = createString(timeStampSplits," ");
            date = getDate(timestamp, dateFormatTZISO);
        }

    } else {
        // Third Case without timezone
        date = getDate(timestamp, dateFormatWithoutTZ);
    }

    System.out.println(date);

    TIMESTAMPTZ oraTimeStamp = new TIMESTAMPTZ(<connection object>,new java.sql.Timestamp(date.getTime());

Above code uses following utility methods

private static Date getDate(String timestamp, SimpleDateFormat dateFormat) {
    Date date = null;
    try {
        date = dateFormat.parse(timestamp);
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return date;
}

private static String createString(String[] contents, String separator) {
    StringBuilder builder = new StringBuilder();
    for (String content : contents) {
        builder.append(content).append(separator);
    }
    builder.deleteCharAt(builder.length()-separator.length());

    return builder.toString();
}

private static String formatTimeZone(String timeZone) {
    String[] timeZoneSplits = timeZone.split(":");
    DecimalFormat formatter = new DecimalFormat("+##;-#");
    formatter.setMinimumIntegerDigits(2);

    timeZoneSplits[0] = formatter.format(Integer.parseInt(timeZoneSplits[0]));
    return createString(timeZoneSplits, ":");
}

This code is specifically written to cater your timestamp examples, any deviation might not be handled by this and it will need more customization.

Hope this helps you.

It really helps in parsing but still I am getting issue in inserting the value of `TIMESTAMPTZ`. It automatically converts these different different time zone values to default database's time zone. e.g The value inserted in database should have been `2003-01-01 02:59:04.123 -08:00` but it was inserted as `2003-01-01 16:29:04.123 Asia/Calcutta`. It converts the value according to `Asia/Calcutta` time zone — Ashish Pancholi, May 06 '16 at 13:39
I never used TIMESTAMPTZ as of now. wherever in my work whenever we had to save dates in a particular timezone then we used Strings to save them. — Sanjeev, May 06 '16 at 18:01

score 1 · Answer 2 · answered May 04 '16 at 12:07

You have to parse the date according to the data coming i.e dynamic. For information about What constant used by android you have to follow the link and in case of Java you have to follow link Here is the code snippet of some different format Sample 1

DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS zzzz");
    Date date = null;
    try {
        date = sdf.parse("2016-04-19 17:34:43.781 Pacific Standard Time");
        Log.e("date",""+date);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    sdf.setTimeZone(TimeZone.getTimeZone("IST"));
    System.out.println(sdf.format(date));

Sample 2

DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
    Date date = null;
    try {
        date = sdf.parse("2016-04-19 17:34:43.781 -08:00");
        Log.e("date",""+date);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    sdf.setTimeZone(TimeZone.getTimeZone("IST"));
    System.out.println(sdf.format(date));

Sample 3

DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    Date date = null;
    try {
        date = sdf.parse("2016-04-19 17:34:43.781");
        Log.e("date",""+date);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    sdf.setTimeZone(TimeZone.getTimeZone("IST"));
    System.out.println(sdf.format(date));

So as per these three set of sample you can parse any type of date time except the one format i.e "2016-04-19 17:34:43.781 Asia/Calcutta" as the time zone Asia/Calcutta or Asia/Bahrain can not get read by android or java. This is the format which gets supported by PHP as per my understanding. SO If you want to parse these type of format then I guess you have to write your custom SimpleDateFormat and have to identify these content and perform the calculation according to your need.

score 0 · Answer 3 · answered May 04 '16 at 03:42

The Timestamp strings are in different format,

 Ex-Here SimpleDateFormat uses pattern :
 'yyyy-MM-dd HH:mm:ss.SSS XXX'

where X is to represent timezone in [ISO 8601 time zone][1] format.For this      
timezone valid Timestamp Strings are (-08; -0800; -08:00).So,'Asia/Kolkata' 
will not be parsed for Sample 1.

There are three type of Timezone pattern to be assigned to SimpleDateFormat.
**'Z'** - RFC 822 time zone.
**'z'** - General time zone.
**'X'** - ISO 8601 time zone.

So,either use different SimpleDateFormat's,or convert Timezone of all timestamp into same pattern of timezone and use a single SimpleDateFormat.

In SimpleDateFormat there are different tags,some of them are in both capital and small letters.So be carefull to use them — Hailey, May 26 '16 at 07:31

Convert `Java.lang.String` TO `oracle.sql.TIMESTAMPTZ`

3 Answers3

Linked