0

I'm struggling from a couple of hours to understand what's going on with the TimeStamps in my code.

Both the Oracle DB and the java application are in PDT

Select from DB:

select id, time_stamp from some_Table where id = '3de392d69c69434eb907f1c0d2802bf0';
3de392d69c69434eb907f1c0d2802bf0    09-DEC-2014 12.45.41.354000000 PM

select id, time_stamp at time zone 'UTC' from some_Table where id = '3de392d69c69434eb907f1c0d2802bf0';
3de392d69c69434eb907f1c0d2802bf0    09-DEC-2014 12.45.41.354000000 PM

The field in the Oracle database is TimeStamp, hence no timezone information is stored.

Timestamp dbTimeStamp = dbRecord.getLastLoginTime();
System.out.println(dbTimeStamp.toString()); // 2014-12-09 12:16:50.365
System.out.println(dbTimeStamp.getTime()); // 1418156210365 --> Tue Dec 09 2014 20:16:50 UTC?

According to the documentation, getTime()

Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Timestamp object.

Why are 8 hours (PDT - UTC) of extra time added to the response of getTime() ?

TJ-
  • 14,085
  • 12
  • 59
  • 90
  • Because as you'll notice from the text you quoted: `since January 1, 1970, 00:00:00 GMT`. Notice the `GMT` there? – Kayaman Dec 09 '14 at 12:46

3 Answers3

4

TimeStamp.toString() internally uses Date.getHours() whose javadoc states:

Returns the hour represented by this Date object. The returned value is a number (0 through 23) representing the hour within the day that contains or begins with the instant in time represented by this Date object, as interpreted in the local time zone.

So toString is using your local time zone whereas getDate doesn't.

Paco Abato
  • 3,920
  • 4
  • 31
  • 54
  • Does this mean that the absolute UTC time here is Tue Dec 09 2014 20:16:50 UTC (the value of the field from the database)? – TJ- Dec 09 '14 at 12:58
  • It seems so. I don't know why the SQL select statement shows differences in minutes and seconds. – Paco Abato Dec 09 '14 at 13:08
3

These two are consistent with each other. The getTime() method gives you the absolute millicecond value, which you chose to interpret in UTC. The toString() method gives you that same millisecond value interpreted in the associated timezone. So it is not getTime() which is adding the time, but toString() which is subtracting it. This is not really documented, but that is how it behaves.

The most important takeaway should be not to rely on Timestamp.toString because it is misleading. The whole timezone mechanism within Date (and Timestamp is a subclass) has been deprecated a long time ago. Instead use just the getTime() value and have it formatted by other APIs, such as Java 8 Date/Time API.

Update

Apparently, the toString() output is actually the correct one, which for me is just one small addition to the thick catalog of all things wrong with Java's date/time handling. You probably receive the timestamp from the database as a formatted string, not the millisecond value. JDBC then parses that into a millisecond value according to the timezone associated with the Timestamp instance, such that the output of toString() matches what was returned by the database, and the actual millisecond value being secondary.

Community
  • 1
  • 1
Marko Topolnik
  • 195,646
  • 29
  • 319
  • 436
  • Does this mean that the absolute UTC time here is `Tue Dec 09 2014 20:16:50 UTC` (which is not consistent with the information I have)? I have added a select from the DB as well. – TJ- Dec 09 '14 at 12:55
  • How can you be sure that output from Oracle isn't adapted to its default timezone? – Marko Topolnik Dec 09 '14 at 12:58
  • I am assuming that because when I executed a select with `at time zone 'UTC'` `-->` It gave the same result. – TJ- Dec 09 '14 at 13:06
  • To make sure this really proves it, try again with `at time zone 'PDT'`. I would also try casting the timestamp as a number, to get the raw value. If it does prove it, then there may be problems interpreting the value at the JDBC side. – Marko Topolnik Dec 09 '14 at 13:07
  • Yes, it returns the same result with `PDT`. What could be wrong here? – TJ- Dec 09 '14 at 13:10
  • I can only guess what Oracle's semantics are for that expression. My guess would be that it has something to do with that column being "without timezone". Anyway, are you sure you have an actual problem? If you have the correct numeric value stored, and have it correctly transferred to a Java Timestamp, then it's just up to correctly interpreting it within Java. – Marko Topolnik Dec 09 '14 at 13:17
  • "Correctly transferred to Java Timestamp" - I executed the same code from 2 different geographies and the value of `dbTimeStamp.getTime()` is different (connecting to the same DB). The translation happens via Hibernate JPA. The locale offset is being used in the translation is my guess. – TJ- Dec 09 '14 at 13:46
  • Sounds like the typical java timezone nightmare. I guess we can assume that the DB responds the same, and with a *string*, which Hibernate then parses into the millisecond value by using the default timezone. Maybe then what is consistent is the `toString` output (which is the exact opposite of what I would consider sane). – Marko Topolnik Dec 09 '14 at 13:53
0

Thanks to the answers above and the references on SO. This answer finally helped me in understanding where I was going wrong in understanding TimeStamps.

Qouting from the linked answer

Note: Timestamp.valueOf("2010-10-23 12:05:16"); means "create a timestamp with the given time in the default timezone".

TimeStamp represents an instant of time. By default, that instant of time in the current Timezone.

The timestamps being written to the DB were UTC instants. i.e. current UTC time was being written. Hence, no matter where the application was deployed, the value being written to the DB was the same TimeStamp.

However, while reading the TimeStamp generated assumes the default TimeZone as read from the deployment JVM. Hence, the value read was the instant in PST timezone. The actual UTC Value being 8 hours more than the PST time. Hence, the difference.

TimeStamp.getTime() returns milliseconds from UTC. TimeStamp.toString() returns the representation of time in the current TimeZone. Thanks @marko-topolnik

To take an example,

  • Value in the DB : 2014-12-09 12:16:50.365
  • When this value is read in a TimeStamp, the instant is 2014-12-09 12:16:50.365 in PST
  • Convert this to UTC, it would be 2014-12-09 20:16:50

Hence, the solution was to add the TimeZone offset to the values read from the database to get the instants as UTC TimeStamps.

The key here was "TimeStamp is a time instant without TimeZone information. The timestamp is assumed to be relative to the default system TimeZone." - It took me a really long while to comprehend this.

Community
  • 1
  • 1
TJ-
  • 14,085
  • 12
  • 59
  • 90