This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 Timestamp - Microsecond Precision

 
Robert Haynes
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The issue at hand has been how to retrieve the current time stamp with the precision out to the microseconds. The basic date formatting only allows for 3 digits and not the 6 desired. The nano seconds seemed way off compared to the mills value being retrieved one line ahead of the nano.

Anyhow, for a DB2 database, the current time stamp can be retrieved in to the Java program very easily. For this resolution, the value retrieved was being passed back to the time stamp parameter of the main SQL statement.


 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
have you tried the rs.getDate method?
which returns a java date
 
Koen Aerts
Ranch Hand
Posts: 344
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy Gibbons wrote:have you tried the rs.getDate method?
which returns a java date

Not sure that would make a difference... java.sql.Timestamp extends from java.util.Date.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Koen Aerts wrote:
Wendy Gibbons wrote:have you tried the rs.getDate method?
which returns a java date

Not sure that would make a difference... java.sql.Timestamp extends from java.util.Date.


I was wondering if getting it as a string was doing anything bad.
 
Robert Haynes
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I have tried them all. While the sql.date extends util.date, the get method only retrieves out to mills and not micro. Converting the time stamp using nano results in a time that is often over a second earlier then the time in mills retrieved. Unless the entire system were to be using nano precision, then any time comparisons would be inaccurate. How accurate all this needs to be is yet to be determined.

I am merely playing in the sandbox now to work out potential issues. If a file in the system used the time stamp as a unique key, then batch processing of records has proven to have the potential for duplicate values when the precision is only mills. At the very least, this is another method available for people to try out so that they can determine what is best for their situation.
 
Rob Spoor
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Java has poor support for finer precision than milliseconds in general. Even System.nanoTime() doesn't return the current time in nanoseconds; just compare the return value of System.nanoTime() to that of System.currentTimeMillis(). The former should be about 1 million times larger, but it usually isn't even close to that.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where do the microsecond timestamps get created? Even with microseconds used as unique keys you're not guaranteed not to have conflicts for two fundamental reasons: firstly, on multi-core systems, two operations can take place at the exact same time, and secondly, even if the producer of the data is not multithreaded, the resolution of a system timer is usually much much more coarse than a microsecond, so two sequentially processed records can still be assigned the same system time. Relying on the system timestamp to be unique exposes you to unpredictable and nasty surprises in the future.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic