Selecting UTC date from Oracle and converting to UTC in Java

user_987925 0
Viewed 1292

If I call in my app:

System.currentTimeMillis()

the result will be, let's say: 1536842771599, which is: GMT: Thursday, September 13, 2018 12:46:11.599 PM OR in my local: Thursday, September 13, 2018 3:46:11.599 PM GMT+03:00 DST

If I do the following select on the Oracle database installed locally:

select CAST(sys_extract_utc(SYSTIMESTAMP) AS DATE) from dual;

I get, through the following JdbcTemplate code:

jdbc.queryForObject(sqlCommand, new Object[0], Timestamp.class);

the following result: enter image description here

Which in my opinion is already wrong because Timestamp usually represents localized time. Further, if I call:

myResult.getTime()

to get the long value, this will convert my time, which was around 12.46 PM, to the value 1536831971599, which is actually: GMT: Thursday, September 13, 2018 9:46:11.599 AM.

Obviously this is not good because it is 2 * GMT+03:00 (6 hours) behind my actual local time, and GMT+03:00 (3 hours) behind UTC time of the Database.

What I'm doing wrong? My only conclusion up until now is to avoid doing a sys_extract_utc on a database and let database return me local time which will be converted to UTC time when I will call getTime on the Timestamp instance.

Any explanation to this?

P.S. Base on the answer that java.sql.Timestamp is UTC, I attach a screenshot of what debugger shows to me, and which is wrong, because it considers the result which is UTC to be GMT+03:00. So, it shows my time as 8.26 + 03:00. My local time is 11.26. If I call getTime on this instance of Timestamp, I will get a long value which if I put into https://www.epochconverter.com/ then I get Friday, September 14, 2018 8:26:06 AM GMT+03:00 DST or Friday, September 14, 2018 5:26:06 AM which is exactly 3 hours behind of what I actually have as UTC!

P.S.2 I use Spring JDBC 4.2.5-RELEASE. Can't use Instant with it, it throws exception that cannot convert Timestamp to Instant because it expects something that derives from Number. I cannot change this JDBC, even version, now, unfortunately.

enter image description here

jdbctemplate timestamp oracle spring java
1 Answers

tl;dr

You misunderstand the java.sql.Timestamp class.

  • java.sql.Timestamp always represents a moment in UTC.
  • java.time.Instant years ago replaced Timestamp.

Use only Instant, never Timestamp.

Instant

The terrible java.sql.Timestamp class is now outmoded by the java.time classes, specifically Instant.

Instant.now()  // Capture the current moment in UTC.

JDBC 4.2

As of JDBC 4.2, we can directly exchange java.time objects with the database. No need to ever again use the awful Date, Calendar, or java.sql classes.

Retrieve.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Insert.

myPreparedStatement.setObject( … , instant ) ;

Timestamp usually represents localized time.

Incorrect. A java.sql.Timestamp represents a moment in UTC, always UTC, by definition. Likewise, Instant too represents a moment in UTC. Both classes represent a count since the first moment of 1970 in UTC.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

user_6427060
Related