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   Cheers  Tim

User · Answer

The solution I opted for was to format the date with the mysql query :

String l_mysqlQuery = "SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s') FROM uld_departure;"
l_importedTable = fStatement.executeQuery( l_mysqlQuery );
System.out.println(l_importedTable.getString( timeIndex));

I had the exact same issue. Even though my mysql table contains dates formatted as such : 2017-01-01 21:02:50

String l_mysqlQuery = "SELECT time FROM uld_departure;"
l_importedTable = fStatement.executeQuery( l_mysqlQuery );
System.out.println(l_importedTable.getString( timeIndex));

was returning a date formatted as such : 2017-01-01 21:02:50.0

User · Answer

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   DATEDate only  no time  no time zone     Time-Of-Day-only   TIME or TIME WITHOUT TIME ZONETime 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 ZONEDate 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 DateSimulates 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 TimeTime 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 TimeStampDate 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     Adjusting time zone  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  amp  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   amp  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  amp  7 in ThreeTen-Backport   Android   Later versions of Android bundle implementations of the java time classes  For earlier Android   lt 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  amp  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

User · 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

User · Answer

this worked        Date date   null      String dateStr   rs getString  doc date        if  dateStr    null            date   dateFormat parse dateStr           using SimpleDateFormat

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

Examples related to java

Examples related to sql

Examples related to oracle

Examples related to resultset

Examples related to getdate