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.

Answer 1

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.

Answer 2

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.