CODEWITHSUNDEEP
javaoracle-databasejdbc
chris01
chris01

Reputation: 12311

Oracle/JDBC: Problem with correct Timezone of rows

I have a table TAB with a timestamp (DT) and a value (VAL) per row.

 DT           NOT NULL   TIMESTAMP(0) WITH LOCAL TIME ZONE
 VAL          NOT NULL   NUMBER

I try to read and get the timestamps in UTC.

SELECT SYS_EXTRACT_UTC (dt) AS dtm, val FROM mytab;

Result in Sqlplus looks ok to me.

DTM                 VAL
---------------------------
30.03.19 23:00:00   124
31.03.19 00:00:00   125
31.03.19 01:00:00   126
31.03.19 02:00:00   127
31.03.19 03:00:00   128
31.03.19 04:00:00   129
31.03.19 05:00:00   130

Now I do the same in Java/JDBC.

java.sql.Timestamp ts = rs.getTimestamp ("dtm");
java.util.Date dt = rs.getDate ("dtm"); 
java.util.Date tm = rs.getTime ("dtm");
ZonedDateTime zdt = ts.toInstant ().atZone (ZoneOffset.UTC);

System.out.println("ts="+ts.toString() + "     val="+rs.getDouble("wert") +
     "   dt=" + dt.toString() + "    tm=" + tm.toString() +"      zdt="+zdt);

And the result is not what I expected regarding timezones.

ts=2019-03-30 23:00:00.0     val=124.0   dt=2019-03-30    tm=23:00:00      zdt=2019-03-30T22:00Z
ts=2019-03-31 00:00:00.0     val=125.0   dt=2019-03-31    tm=00:00:00      zdt=2019-03-30T23:00Z
ts=2019-03-31 01:00:00.0     val=126.0   dt=2019-03-31    tm=01:00:00      zdt=2019-03-31T00:00Z
ts=2019-03-31 03:00:00.0     val=127.0   dt=2019-03-31    tm=02:00:00      zdt=2019-03-31T01:00Z
ts=2019-03-31 03:00:00.0     val=128.0   dt=2019-03-31    tm=03:00:00      zdt=2019-03-31T01:00Z
ts=2019-03-31 04:00:00.0     val=129.0   dt=2019-03-31    tm=04:00:00      zdt=2019-03-31T02:00Z
ts=2019-03-31 05:00:00.0     val=130.0   dt=2019-03-31    tm=05:00:00      zdt=2019-03-31T03:00Z

Why does ts have a double 3? I would expect it to be in UTC. The only correct time is in tm.

Why is that? Whats the problem here?

JRE 1.8 Oracle 12c Linux with local timezone CET/CEST

Upvotes: 0

Views: 1306

Answers (2)

Andreas
Andreas

Reputation: 159086

Timezone CET/CEST switches to Daylight Savings Time on 2019-03-31 at 02:00:00, so it skips from 02:00:00 to 03:00:00.

getDate returns a java.sql.Date that is a java.util.Date at 2019-03-31 at midnight, so no DST.

getTime returns a java.sql.Time that is a java.util.Date at 1970-01-01 at given time, so no DST.

getTimestamp returns a java.sql.Timestamp that is a java.util.Date at given date and time, so DST is applied, changing 02:00 to 03:00. However, the values from the database don't have time zone, so remaining values don't get shifted. This is a flaw in the handling of TIMESTAMP WITH LOCAL TIME ZONE.

Converting java.sql.Timestamp to java.time.Instant will adjust for DST, so mangles the times.

Note that TIMESTAMP WITH LOCAL TIME ZONE does not store a time zone, it just tell the client API to convert to session time zone on your behalf. The actual value in the database depends on the database time zone, not the client time zone or the session time zone.

Upvotes: 2

Muhammed Imran Hussain
Muhammed Imran Hussain

Reputation: 2115

From Java Doc: https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html Timestamp, A thin wrapper around java.util.Date that allows the JDBC API to identify this as an SQL TIMESTAMP value. It adds the ability to hold the SQL TIMESTAMP fractional seconds value, by allowing the specification of fractional seconds to a precision of nanoseconds. A Timestamp also provides formatting and parsing operations to support the JDBC escape syntax for timestamp values.

The precision of a Timestamp object is calculated to be either:

  • 19 , which is the number of characters in yyyy-mm-dd hh:mm:ss
  • 20 + s , which is the number of characters in the yyyy-mm-dd hh:mm:ss.[fff...] and s represents the scale of the given Timestamp, its fractional seconds precision.

There are many ways to format the timestamp and one such example can be following:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Timestamp ts = Timestamp.valueOf("2019-03-30 23:00:00.0");
System.out.println(sdf.format(ts));

Upvotes: 1

Related Questions