3

In my Java app I must save data to Oracle 11g with object created date and time and for this I convert java.util.Date() to java.sql.Date() in format as new java.sql.Date(new java.util.Date().getTime()). But I noticed, that after the data inserted, oracle truncate the time part of date and I get something like 16/09/2015. but I need format like this: 16/09/2015 9:55:44. the second format created by oracle's sysdate() procedure. How I can get the second format from java code?

Jack Daniel
  • 2,397
  • 8
  • 33
  • 58
  • 2
    Have you tried using `java.sql.TimeStamp`? – MadProgrammer Sep 16 '15 at 06:06
  • 2
    `java.sql.Date()` removes the time part **by definition** (see the [JavaDocs](http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html)). You need to use `ResultSet.getTimestamp()` or `PreparedStatement.setTimestamp()` if you want to store the time as well. –  Sep 16 '15 at 06:10
  • I will check it. tnx. – Jack Daniel Sep 16 '15 at 06:16
  • @MadProgrammer ur answer was first and it's a solution. u can write it as answer and I'll accept it – Jack Daniel Sep 16 '15 at 06:38
  • See this similar Question with much discussion: [java.util.Date vs java.sql.Date](http://stackoverflow.com/q/2305973/642706). – Basil Bourque Sep 16 '15 at 08:47

3 Answers3

4

From memory (it's been a while), java.sql.Date is used to hold dates (no time) only, if you want time information as well, you need to use java.sql.TimeStamp instead.

MadProgrammer
  • 343,457
  • 22
  • 230
  • 366
  • It _should_ be true for all databases that are accessed through JDBC, because the JDBC API defines that `java.sql.Date` sets the time part to `00:00:00` –  Sep 16 '15 at 06:46
  • @a_horse_with_no_name I can't find anything in the docs which seems to support that, the JavaDocs says *"A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT."* which seems to indicate it's capable of supporting time, but I guess that comes down to how the JDBC driver maps it's column values. From my experience, it seems to hold true though (`Date` for dates, `TimeStamp` for date/time) – MadProgrammer Sep 16 '15 at 06:50
  • 1
    The next sentence is the [documentation](http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html) of `java.sql.Date` is: *To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.* So @a_horse_with_no_name is right. – Codo Sep 16 '15 at 06:56
  • @Codo I'd be nice if it just said "does not carry time values" :P – MadProgrammer Sep 16 '15 at 06:58
  • @MadProgrammer: it can't say that because `java.sql.Date` inherits from `java.util.Date` and that always contains a time part. –  Sep 16 '15 at 07:00
  • @a_horse_with_no_name It could indicate better that the time is truncated - lots of words meaning the same thing, I understand that `java.sql.Date` and `java.sql.TimeStamp` extends from `java.util.Date`, it's about making the documentation more clearly stated towards the intent, that's all :P – MadProgrammer Sep 16 '15 at 07:06
  • Hmm, "*by setting the hours, minutes, seconds, and milliseconds to zero*" couldn't be any clearer to me. –  Sep 16 '15 at 07:11
  • @a_horse_with_no_name Nice for you, I skipped right over it. Simply saying that the "time part is truncated" or "ignored" in favor of the "date part only" must have been to hard :P - The statement still leaves to much ambiguity for me – MadProgrammer Sep 16 '15 at 07:13
1

Do not use java.sql.Date if you want to store date and time. Just use java.util.Date without any conversion. Simple and easy. There is no need for java.sql.TimeStamp either.

And make sure your NLS settings (e.g. in SQL Developer) are such that they display both date and time as Oracle does not distinguish between dates with and without time.

Codo
  • 75,595
  • 17
  • 168
  • 206
0

I guess Oracle does not truncate. For example, if you use Oracle SQL Developer, you have to update NLS parameter "Date Format" to see a time.

By default it just equal "DD-MON-RR"

enter image description here

Alex Karasev
  • 1,108
  • 2
  • 13
  • 24
  • no, Oracle truncate, I use Toad and in Toad the data created with sysdate showed with date and time, but inserted from java as date only – Jack Daniel Sep 16 '15 at 06:16
  • Then, I think, Toad just truncate zero values of HH22:MI:SS, because `To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.` http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html – Alex Karasev Sep 16 '15 at 06:26