JDBC ResultSet: I need a getDateTime, but there is only getDate and getTimeStamp

Question

I would like to get the DATETIME column from an Oracle DB Table with JDBC. Here is my code:

int columnType = rsmd.getColumnType(i);
if(columnType == Types.DATE)
{
    Date aDate = rs.getDate(i);
    valueToInsert = aDate.toString();
}
else if(columnType == Types.TIMESTAMP)
{
    Timestamp aTimeStamp = rs.getTimestamp(i);
    valueToInsert = aTimeStamp.toString();
}
else
{
    valueToInsert = rs.getString(i);
}

I have to identify the column type first. The field I am interested in is recognized as a Types.DATE, but it is really a DATETIME in the DB since it has this format: "07.05.2009 13:49:32"

getDate truncates the time: "07.05.2009" and getString appends ".0" to it: "07.05.2009 13:49:32.0"

Of course I could just remove the final .0 and work with getString all the time, but it is a dirty workaround.

Any ideas? I was looking for a getDateTime method.

Solution 1

This answer is outdated. Continue to Basil Bourque's answer.

java.util.Date date;
Timestamp timestamp = resultSet.getTimestamp(i);
if (timestamp != null)
    date = new java.util.Date(timestamp.getTime()));

Then format it the way you like.

Solution 2

The answer by Leos Literak is correct but now outdated, using one of the troublesome old date-time classes, java.sql.Timestamp.

tl;dr

it is really a DATETIME in the DB

Nope, it is not. No such data type as DATETIME in Oracle database.

I was looking for a getDateTime method.

Use java.time classes in JDBC 4.2 and later rather than troublesome legacy classes seen in your Question. In particular, rather than java.sql.TIMESTAMP, use Instant class for a moment such as the SQL-standard type TIMESTAMP WITH TIME ZONE.

Contrived code snippet:

if( 
    JDBCType.valueOf( 
        myResultSetMetaData.getColumnType( … )
    )
    .equals( JDBCType.TIMESTAMP_WITH_TIMEZONE ) 
) {
    Instant instant = myResultSet.getObject( … , Instant.class ) ;
}

Oddly enough, the JDBC 4.2 specification does not require support for the two most commonly used java.time classes, Instant and ZonedDateTime. So if your JDBC does not support the code seen above, use OffsetDateTime instead.

OffsetDateTime offsetDateTime = myResultSet.getObject( … , OffsetDateTime.class ) ;

Details

I would like to get the DATETIME column from an Oracle DB Table with JDBC.

According to this doc, there is no column data type DATETIME in the Oracle database. That terminology seems to be Oracle’s word to refer to all their date-time types as a group.

I do not see the point of your code that detects the type and branches on which data-type. Generally, I think you should be crafting your code explicitly in the context of your particular table and particular business problem. Perhaps this would be useful in some kind of generic framework. If you insist, read on to learn about various types, and to learn about the extremely useful new java.time classes built into Java 8 and later that supplant the classes used in your Question.

Smart objects, not dumb strings

valueToInsert = aDate.toString();

You appear to trying to exchange date-time values with your database as text, as String objects. Don’t.

To exchange date-time values with your database, use date-time objects. Now in Java 8 and later, that means java.time objects, as discussed below.

Various type systems

You may be confusing three sets of date-time related data types:

• Standard SQL types
• Proprietary types
• JDBC types

SQL standard types

The SQL standard defines five types:

• DATE
• TIME WITHOUT TIME ZONE
• TIME WITH TIME ZONE
• TIMESTAMP WITHOUT TIME ZONE
• TIMESTAMP WITH TIME ZONE

Date-only

• DATE
  Date only, no time, no time zone.

Time-Of-Day-only

• TIME or TIME WITHOUT TIME ZONE
  Time only, no date. Silently ignores any time zone specified as part of input.
• TIME WITH TIME ZONE (or TIMETZ)
  Time only, no date. Applies time zone and Daylight Saving Time rules if sufficient data is included with input. Of questionable usefulness given the other data types, as discussed in Postgres doc.

Date And Time-Of-Day

• TIMESTAMP or TIMESTAMP WITHOUT TIME ZONE
  Date and time, but ignores time zone. Any time zone information passed to the database is ignores with no adjustment to UTC. So this does not represent a specific moment on the timeline, but rather a range of possible moments over about 26-27 hours. Use this if the time zone or offset are (a) unknown or (b) irrelevant such as "All our factories around the world close at noon for lunch". If you have any doubts, not likely the right type.
• TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ)
  Date and time with respect for time zone. Note that this name is something of a misnomer depending on the implementation. Some systems may store the given time zone info. In other systems such as Postgres the time zone information is not stored, instead the time zone information passed to the database is used to adjust the date-time to UTC.

Proprietary

Many database offer their own date-time related types. The proprietary types vary widely. Some are old, legacy types that should be avoided. Some are believed by the vendor to offer certain benefits; you decide whether to stick with the standard types only or not. Beware: Some proprietary types have a name conflicting with a standard type; I’m looking at you Oracle DATE.

JDBC

The Java platform's handles the internal details of date-time differently than does the SQL standard or specific databases. The job of a JDBC driver is to mediate between these differences, to act as a bridge, translating the types and their actual implemented data values as needed. The java.sql.* package is that bridge.

JDBC legacy classes

Prior to Java 8, the JDBC spec defined 3 types for date-time work. The first two are hacks as before Version 8, Java lacked any classes to represent a date-only or time-only value.

• java.sql.Date
  Simulates a date-only, pretends to have no time, no time zone. Can be confusing as this class is a wrapper around java.util.Date which tracks both date and time. Internally, the time portion is set to zero (midnight UTC).
• java.sql.Time
  Time only, pretends to have no date, and no time zone. Can also be confusing as this class too is a thin wrapper around java.util.Date which tracks both date and time. Internally, the date is set to zero (January 1, 1970).
• java.sql.TimeStamp
  Date and time, but no time zone. This too is a thin wrapper around java.util.Date.

So that answers your question regarding no "getDateTime" method in the ResultSet interface. That interface offers getter methods for the three bridging data types defined in JDBC:

• getDate for java.sql.Date
• getTime for java.sql.Time
• getTimestamp for java.sql.Timestamp

Note that the first lack any concept of time zone or offset-from-UTC. The last one, java.sql.Timestamp is always in UTC despite what its toString method tells you.

JDBC modern classes

You should avoid those poorly-designed JDBC classes listed above. They are supplanted by the java.time types.

• Instead of java.sql.Date, use LocalDate. Suits SQL-standard DATE type.
• Instead of java.sql.Time, use LocalTime. Suits SQL-standard TIME WITHOUT TIME ZONE type.
• Instead of java.sql.Timestamp, use Instant. Suits SQL-standard TIMESTAMP WITH TIME ZONE type.

As of JDBC 4.2 and later, you can directly exchange java.time objects with your database. Use setObject/getObject methods.

Insert/update.

myPreparedStatement.setObject( … , instant ) ;

Retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

If you want to see the moment of an Instant as viewed through the wall-clock time used by the people of a particular region (a time zone) rather than as UTC, adjust by applying a ZoneId to get a ZonedDateTime object.

ZoneId zAuckland = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdtAuckland = instant.atZone( zAuckland ) ;

The resulting ZonedDateTime object is the same moment, the same simultaneous point on the timeline. A new day dawns earlier to the east, so the date and time-of-day will differ. For example, a few minutes after midnight in New Zealand is still “yesterday” in UTC.

You can apply yet another time zone to either the Instant or ZonedDateTime to see the same simultaneous moment through yet another wall-clock time used by people in some other region.

ZoneId zMontréal = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdtMontréal = zdtAuckland.withZoneSameInstant( zMontréal ) ;  // Or, for the same effect: instant.atZone( zMontréal ) 

So now we have three objects (instant, zdtAuckland, zMontréal) all representing the same moment, same point on the timeline.

Detecting type

To get back to the code in Question about detecting the data-type of the databases: (a) not my field of expertise, (b) I would avoid this as mentioned up top, and (c) if you insist on this, beware that as of Java 8 and later, the java.sql.Types class is outmoded. That class is now replaced by a proper Java Enum of JDBCType that implements the new interface SQLType. See this Answer to a related Question.

This change is listed in JDBC Maintenance Release 4.2, sections 3 & 4. To quote:

Addition of the java.sql.JDBCType Enum

An Enum used to identify generic SQL Types, called JDBC Types. The intent is to use JDBCType in place of the constants defined in Types.java.

The enum has the same values as the old class, but now provides type-safety.

A note about syntax: In modern Java, you can use a switch on an Enum object. So no need to use cascading if-then statements as seen in your Question. The one catch is that the enum object’s name must be used unqualified when switching for some obscure technical reason, so you must do your switch on TIMESTAMP_WITH_TIMEZONE rather than the qualified JDBCType.TIMESTAMP_WITH_TIMEZONE. Use a static import statement.

So, all that is to say that I guess (I’ve not tried yet) you can do something like the following code example.

final int columnType = myResultSetMetaData.getColumnType( … ) ;
final JDBCType jdbcType = JDBCType.valueOf( columnType ) ;

switch( jdbcType ) {  

    case DATE :  // FYI: Qualified type name `JDBCType.DATE` not allowed in a switch, because of an obscure technical issue. Use a `static import` statement.
        …
        break ;

    case TIMESTAMP_WITH_TIMEZONE :
        …
        break ;

    default :
        … 
        break ;

}

---

---

About java.time

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

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

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

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

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.

---

UPDATE: The Joda-Time project, now in maintenance mode, advises migration to the java.time classes. This section left intact as history.

Joda-Time

Prior to Java 8 (java.time.* package), the date-time classes bundled with java (java.util.Date & Calendar, java.text.SimpleDateFormat) are notoriously troublesome, confusing, and flawed.

A better practice is to take what your JDBC driver gives you and from that create Joda-Time objects, or in Java 8, java.time.* package. Eventually, you should see new JDBC drivers that automatically use the new java.time.* classes. Until then some methods have been added to classes such as java.sql.Timestamp to interject with java.time such as toInstant and fromInstant.

String

As for the latter part of the question, rendering a String… A formatter object should be used to generate a string value.

The old-fashioned way is with java.text.SimpleDateFormat. Not recommended.

Joda-Time provide various built-in formatters, and you may also define your own. But for writing logs or reports as you mentioned, the best choice may be ISO 8601 format. That format happens to be the default used by Joda-Time and java.time.

Example Code

//java.sql.Timestamp timestamp = resultSet.getTimestamp(i);
// Or, fake it 
// long m = DateTime.now().getMillis();
// java.sql.Timestamp timestamp = new java.sql.Timestamp( m );

//DateTime dateTimeUtc = new DateTime( timestamp.getTime(), DateTimeZone.UTC );
DateTime dateTimeUtc = new DateTime( DateTimeZone.UTC ); // Defaults to now, this moment.

// Convert as needed for presentation to user in local time zone.
DateTimeZone timeZone = DateTimeZone.forID("Europe/Paris");
DateTime dateTimeZoned = dateTimeUtc.toDateTime( timeZone );

Dump to console…

System.out.println( "dateTimeUtc: " + dateTimeUtc );
System.out.println( "dateTimeZoned: " + dateTimeZoned );

When run…

dateTimeUtc: 2014-01-16T22:48:46.840Z
dateTimeZoned: 2014-01-16T23:48:46.840+01:00

Comments

[Admin]

I am using JTDS with MSSQL, when I am doing rs.getString("000123450"), it's removing all leading zero's and giving me 123450 in the output. How I can get this whole values like 000123450? Any help? Thanks, Harshita

[Basil Bourque]

@user4567570 Please re-read my Answer. Generally you should not be using strings for date-time work. Use date-time types in your database, and date-time types (java.sql.*) in your JDBC code, and date-time types (java.time.*) in your Java code. That's general advice, as I'm not familiar with JTDS nor MSSQL.

[Zdeněk Pavlas]

Wouldn't a simple cast work, too? public class Timestamp extends java.util.Date

[Namphibian]

An update for Java 8 since it is now a reality would make this answer really shine... nudge nudge wink wink.

[Hakanai]

Beware of time zones when using this answer. getTimestamp() for some drivers (definitely for JTDS) can default to the time zone of the current computer, and if your database is storing timestamps as year-month-day hour:minute:second, this will make you get the wrong values. Note that there is an overload taking Calendar which may not exhibit the issue.

[theyuv]

@Trejkaz how do you recommend to overcome the issue you mention?

[Hakanai]

@theyuv we intend to avoid it by switching to BIGINT for all timestamp columns and using Instant in the client code, so that time zones never have to come into the picture. (And also, like I already mentioned, there is the Calendar overload, which some people claim works for some databases, but I prefer to ditch old date/time APIs where possible.)

[Leos Literak]

But you still need to know the timezone of bigint value, do not you?

[Basil Bourque]

@Namphibian Ask, and ye shall receive.

[S'chn T'gai Spock]

To overcome the problem mentioned in 2nd comment, I recommend to use resultSet.getTimestamp(i, Calendar.getInstance(TimeZone.getTimeZone("GMT")));

[Douglas Surber]

I know this is years later, but for others who find this answer.... This is an excellent answer over all, but there is one problem. There are very few uses for java.time.Instant. As a general rule don't use it. Use OffsetDateTime instead. Instant is basically the same as the UTC time standard (not UTC the time zone with zero offset). OffsetDateTime refers to the time as indicated by a calendar and a clock. This is different from UTC. Almost all uses of time are about calendar and clock time, not the atomic time standard. Also OffsetDateTime is much more capable.

[Douglas Surber]

My previous comment also explains why the JDBC spec doesn't cover Instant; it shouldn't be used. OffsetDateTime is almost invariably a better choice. ZonedDateTime is not included in the spec because SQL doesn't include named time zones, only zone offsets which are represented by OffsetDateTime. Some databases (Oracle for one) support named time zones and Oracle Database JDBC supports ZonedDateTIme.