0

In my Java code, I'm selecting/fetching an Oracle DATE column using ResultSet and getTimestamp() method, and converting this to time-in-milli-seconds. The problem is the time-in-milli-seconds varies across the machines I'm running it on. For example, if the actual timestamp is 1511213580 ms, on machine1 this is 1511262180 ms and on machine2 it is 1511233380 ms.

I've read this post Is java.sql.Timestamp timezone specific? and understand that Timestamp uses the machine's equivalent timezone to store this data.

My question is, how do I standardise the timestamps to display/read the same across clients? I do not have access to the code that persists the timestamps. On the machines that run this fetch-from-db program, I'm using additional shell scripts to compare the time-in-milli-seconds with the current time from the machine.

Here are my code snippets:

Timestamp timestamp = rset.getTimestamp(i);
if (timestamp != null)
    timeInSeconds = timestamp.getTime()/1000;
Usagi Miyamoto
  • 6,196
  • 1
  • 19
  • 33
julie
  • 37
  • 1
  • 8
  • Also, the machines are located in different time zones. The DB value is compared against the 'current' time (on the machine) and relevantly a different process is run. The fact that the machines are interpreting the DB value differently is hindering the downstream process. Thanks. – julie Nov 22 '17 at 11:08

1 Answers1

0

tl;dr

myResultSet.getObject( … , Instant.class ) 
           .isBefore( Instant.now() )

Details

As has been discussed many times on Stack Overflow, you should:

  • Store your date-time values in UTC.
  • Never depend on the JVM’s current default time zone.
  • Avoid the terrible legacy date-time classes such as Date, Calendar, and the java.sql.* classes.
  • Use only the java.time rather than the legacy date-time classes they supplant.
  • Use a driver compliant with JDBC 4.2 or later to retrieve java.time objects.
  • Avoid tracking time as a count-from-epoch, using objects instead.

I’m not an Oracle user, but it appears that DATE is a legacy type, lacks any concept of time zone. I suggest using the standard TIMESTAMP WITH TIME ZONE instead.

An Instant is a moment on the timeline, always in UTC, with a resolution of nanoseconds. 

Instant instant = myResultSet.getObject( … , Instant.class ) ;
Boolean isPast = instant.isBefore( Instant.now() ) ;

While I advise against tracking time as a count from epoch, if you insist, you can extract a count of milliseconds since the epoch of 1970-01-01T00:00:00Z. Beware of data loss as you are truncating any microseconds or nanoseconds that may be present.

long millis = instant.toEpochMilli() ;

About java.sql.Timestamp, these objects are always in UTC. This confusing legacy class, badly designed as a hack, is now legacy and should be avoided. Replaced by java.time.Instant.

The problem is the time-in-milli-seconds varies across the machines I'm running it on. For example, if the actual timestamp is 1511213580 ms, on machine1 this is 1511262180 ms and on machine2 it is 1511233380 ms.

That makes no sense as java.sql.Timestamp will contain the same count from epoch in UTC across machines. I suspect the clocks of those alternate machines have been set to the wrong time, perhaps intentionally as a misguided attempt to handle time zone adjustment.

In that case you have a mess on your hands. The only workaround is to test each machine, calculate the delta between true UTC time and that particular machine’s incorrect clock. Then add or subtract that amount when obtaining data from that machine. Obviously risky as you never know when that confused sysadmin will attempt another adjustment hack.

The real solution is to: keep all servers in UTC and set to true accurate clock time, handle date-time values with java.time classes, and store moments in a column of type TIMESTAMP WITH TIME ZONE.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Dear Down-Voter, please leave a criticism along with your vote. – Basil Bourque Nov 22 '17 at 16:56
  • I didn't downvote, but the question was stating that they have no influence on the code that's storing the date. So this is not an answer to the particular problem. – Crusha K. Rool Nov 22 '17 at 17:50
  • @CrushaK.Rool My Answer shows code for retrieving data from the database, not storing the data. – Basil Bourque Nov 22 '17 at 18:00
  • Right, I was for some reason assuming that the data might be stored with different time zone data (non-UTC) as well and that this would make your first point moot, but that should hopefully not be the case. So your code should work. The `Date` type in Oracle is indeed a very old type and also has the problem that it can't store fractional seconds, so there is some significant rounding going on that might be a problem depending on the required granularity of the application. – Crusha K. Rool Nov 22 '17 at 19:36
  • Thank you @BasilBourque for your detailed post, appreciate it. – julie Nov 24 '17 at 05:09