• 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
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

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

 
Ranch Hand
Posts: 240
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.
 
Saloon Keeper
Posts: 21306
140
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: 240
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
Saloon Keeper
Posts: 21306
140
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.
 
I have always wanted to have a neighbor just like you - Fred Rogers. Tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!