This week's book giveaway is in the Java in General forum.
We're giving away four copies of Event Streams in Action and have Alexander Dean & Valentin Crettaz on-line!
See this thread for details.
Win a copy of Event Streams in Action this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Why does Oracle store timestamp with second & milli seconds for a LocalDateTime?

 
Ranch Hand
Posts: 233
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I'm using Hibernate 5.2 and Java 8.
When I persist a POJO object that has LocalDateTime instance. The value of this LocalDateTime instance I provide is just Date+Hours+Minutes "LocalDateTime.of(2019, 7, 25, 12, 47)", Then it is stored in oracle db as a complete timestamp "25-JUL-19 12.47.00.000000 PM."
The problem is, if you want to retrieve a record by its date then you have to provide this complete value with seconds & milliseconds as well.
Even if you try to retrieve the record in 'Oracle DB login', you have to provide the complete value.
How do i fix this?

Thanks in advance.
 
Bartender
Posts: 20928
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's best to treat dates and times in databases the same way you'd treat floating-point numbers. That is, as inexact values that you should avoid testing (or searching) for based on equality.

The java.lang.Date and javax.sql.Date classes hold times with a granularity of milliseconds. However, as I recall, the standard Oracle granularities for timestamps is either in days or in whole seconds. I forget what they have for finer-grained time, but I'm pretty sure it's something that would get truncated if loaded into javax.sql.Date.

So in your Java code, you'll generally see millisecond precision, but if you store that item to Oracle and fetch it back, it will only be precise to integral seconds. Be warned. If you want more precision you'll have to go to greater effort.

And remember, this is Oracle. Other DBMS's have different granularities.
 
Arun Singh Raaj
Ranch Hand
Posts: 233
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, how do I save only the time (not date+time) into table in  oracle? Since the datatype is Timestamp for even time, it stores date+time.
Thanks
 
Tim Holloway
Bartender
Posts: 20928
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe that Oracle also has a pure Date column type with no time. Don't forget, however, that unless you're working with data that's limited to a single location or context, you're going to need the time as well, since it can often be Sunday in New York when it's already Monday in Mumbai.

When you persist a standard Java Date object to Oracle, the parts of that Date object that won't fit (such as hours/minutes/seconds on a Date column) will be truncated. Lost forever.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!