Conversion of string with AM/PM date-time, from Oracle database

Question

I have the timestamp in form of 03-AUG-12 08.15.00.000000000 PM -05:00 I am unable to get a String representation in form on yyyy-MM-dd HH:mm:ss.

Here is my code :

public static void convert() {

    String oldstring = "03-AUG-12 08.15.00.000000000 PM -05:00";
    Date date = null;
    try {
        date = new SimpleDateFormat("dd-MMM-yy HH.mm.ss.S aa").parse(oldstring);
    }
    catch (ParseException e) {
        e.printStackTrace();
    }

    String newstring = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
    System.out.println(newstring);
}

Basically it is a timestamp with timezone format from the Oracle database.

Solution 1

You can't use SimpleDateFormat to parse such a string, at least not without some limitations:

• A time zone designator like -05:00 (according to ISO 8601) is not supported until Java 7. With Java 7 you can use the XXX pattern to parse it.

• To parse the month name correctly, you should specify that you require an English locale.

• The pattern for milliseconds (S) parses an unlimited number of digits. If your string contains "08.15.00.100000000", SimpleDateFormat would parse this as 8:15:00 and 100000000ms, adding almost 28 hours to the expected value. If you are sure that the value is always 0, you can ignore this problem.

If you can accept the last issue and use Java 7, you should be using something like this:

new SimpleDateFormat("dd-MMM-yy hh.mm.ss.S aa XXX", Locale.ENGLISH)

Solution 2

Change this line like this:

        date = new SimpleDateFormat("dd-MMM-yy hh.mm.ss.S aa").parse(oldstring);

You need to use the lowercase h which parses the AM/PM hours 1-12.

Solution 3

tl;dr

DateTimeFormatter f = new DateTimeFormatterBuilder ().parseCaseInsensitive ().appendPattern ( "dd-MMM-yy hh.mm.ss.SSSSSSSSS a ZZZZZ" ).toFormatter ().withLocale ( Locale.US );
OffsetDateTime odt = OffsetDateTime.parse ( "03-AUG-12 08.15.00.000000000 PM -05:00" , f );

Objects, not strings

You mentioned Oracle. When retrieving data from a database, use objects rather than strings. Ask your ResultSet for a java.sql.Timestamp object.

java.sql.Timestamp ts = myResultSet.getTimestamp( … );

Convert from the troublesome old date-time class to the modern java.time type. Look to new methods added on the old classes to facilitate conversion to/from java.time.

Instant instant = ts.toInstant();

Or preferably, if using JDBC 4.2 or later, and using Java 8 or later, you may be able to retrieve an java.time.Instant via the ResultSet::getObject method.

But if you must parse a string, read on.

Avoid old date-time classes

The old date-time classes bundled with the earliest versions of Java are troublesome and confusing. Now supplanted by the java.time classes.

Nanoseconds

That bunch of zeros must represent a fraction of a second with nanosecond resolution, up to nine digits of a decimal fraction. The old date-time classes can handle only milliseconds, but fortunately the modern java.time classes can indeed handle a resolution up to nanoseconds.

Using java.time

Define a formatting pattern to parse the given input. By the way, your data’s format is less than optimal; in the future, use ISO 8601 standard formats for strings that represent date-time values.

We use a “builder” in order to specify case-insensitive parsing. Proper abbreviation for a name of month in English is initial-cap, Aug, but your input uses all-caps.

String pattern = "dd-MMM-yy hh.mm.ss.SSSSSSSSS a ZZZZZ";
DateTimeFormatterBuilder fb = new DateTimeFormatterBuilder ().parseCaseInsensitive ().appendPattern ( pattern );

Tell the builder to instantiate our formatter. The Locale to determines (a) the human language for translation of name of day, name of month, and such, and (b) the cultural norms deciding issues of abbreviation, capitalization, punctuation, and such.

Locale l = Locale.US;
DateTimeFormatter f = fb.toFormatter ().withLocale ( l );

Finally, parse the string as an OffsetDateTime object.

String input = "03-AUG-12 08.15.00.000000000 PM -05:00";
OffsetDateTime odt = OffsetDateTime.parse ( input , f );

Dump to console. Note how our 8 PM is transformed into the 24-hour clock value of 20 hours.

System.out.println ( "input: " + input + " | odt.toString(): " + odt );

input: 03-AUG-12 08.15.00.000000000 PM -05:00 | odt.toString(): 2012-08-03T20:15-05:00

A time zone is an offset-from-UTC plus a set of rules for handling anomalies such as Daylight Saving Time (DST). If desired, apply a ZoneId to get a ZonedDateTime.

ZoneId z = ZoneId.of( “America/Montreal” );
ZonedDateTime zdt = odt.atZoneSameInstant( z );

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old date-time classes such as java.util.Date, .Calendar, & java.text.SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to java.time.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations.

Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP (see How to use…).

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Comments

[Java Not Coffee]

@Jambjo - Thanks a ton for that insight into Java 7 feature as I wasn't aware of that. How shall I successfully fetch that value (from the column of the type Timezone with Timestamp, in the oracle db) in a resultset, as I don't see a resultset operation with resultSet.getTimeStampWithTimezone or something similar? while(resultSet.next()){ java.sql.Timestamp getTimeStamp = resultSet.getTimestamp("date_val"); } I am currently using Java 6. Kindly help me with this. I need to fetch from db, parse it to the format I mentioned in the question and retrieve a string. Waiting for an answer.

[jarnbjo]

Why do you need the time zone from the database? What is wrong with resultSet.getTimestamp()?

[Ashutosh Jindal]

Thanks for mentioning the lowercase h. Danke!