Menu
This question is related to this but this specific question focuses on the why. So no, this isn't a duplicate.
Quoting the answer:
The problem is that Java Date objects don't store a time zone. The value is always in UTC, and is parsed and formatted in a given time zone, usually the JVM's default time zone.
Oracle DATE columns are also stored without time zone, but should represent the date as seen by the user. In 99.99% of cases, that means the date in the JVM's default time zone.
So, the JDBC driver takes the Timestamp / Date value, which is in UTC, converts it to the default time zone, and saves that to the database.
The answers to these questions are the whys.
I couldn't see the benefit of the design and I can only see are the problems associated with it. Case in point is when saving is done in a specific timezone and retrieval is done in another timezone. The amount of questions being thrown at this specific topic just proves my point.
So ultimate question is, why it was designed that way? What are the reasons?
448
Date-time handling is a surprisingly complicated topic. Our intuitive understanding of time works against us as programmers, making this topic difficult to master. Furthermore, poor date-time handling in old databases and old classes make the job even more confusing.
Firstly, avoid the wretched old date-time classes bundled with the earliest versions of Java. Never use java.util.Date, java.util.Calendar, java.sql.Timestamp, nor other related classes. Use only the java.time classes. If you must interface with old code not yet updated to java.time, call on new conversions methods added to the old classes.
Date was replaced by Instant.
The problem is that Java Date objects don't store a time zone.
Not true. An Instant (and a Date) is always in UTC. Both the modern and legacy class represent a a count of fractional seconds since the first moment of 1970 in UTC, 1970-01-01T00:00:00Z. Always in UTC, easy-peasy.
Oracle DATE columns are also stored without time zone,
True.
The Oracle DATE data type represents only a date with time zone but lacks any concept of time zone or offset-from-UTC. This is apparently a legacy type, created before the SQL standard defined some basic date-time types. In the standard, the TIMESTAMP WITHOUT TIME ZONE might map close to Oracle DATE.
but should represent the date as seen by the user. In 99.99% of cases, that means the date in the JVM's default time zone.
I have no idea what the author meant by that. I think that is their clumsy way of saying that any type similar to the SQL-standard TIMESTAMP WITHOUT TIME ZONE simply takes any given date or date-with-time-of-day as-is, with no attempt at adjusting between zones or offsets. So if you pass January 21, 2018 at noon, it stores a value equivalent to this string 2018-01-23T12:00 without any regard to whether that was noon in Montréal Québec or noon in Kolkata India (two different moments, hours apart).
So, the JDBC driver takes the Timestamp / Date value, which is in UTC, converts it to the default time zone, and saves that to the database.
While the JDBC driver is unspecified here, I doubt this is its behavior. Such behavior would be a contradiction of the behavior of the Oracle DATE type which is no such zone adjustment. The Oracle DATE type (as I read the documentation; I'm not an Oracle user) is agnostic or unaware of zones/offsets.
In Java, the class mapping to SQL-standard TIMESTAMP WITHOUT TIME ZONE and Oracle DATE is LocalDateTime. You should use these zone-less types only in these three situations:
LocalDateTime. To print a report or display a calendar, dynamically apply a time zone (ZoneId) to generate a specific moment (ZonedDateTime or Instant). This must be done on-the-fly rather than storing the value.What's exactly wrong with NOT adjusting and saving the value (UTC) as it is?
The JDBC driver should not be doing any adjustments to UTC for a type of Oracle Date or SQL-standard TIMESTAMP WITHOUT TIME ZONE.
If two users at 2018-06-06T21:53Z, one in Québec and one in India, both simultaneously save the current moment of their own parochial wall-clock time into a column of type SQL-standard TIMESTAMP WITHOUT TIME ZONE or Oracle DATE, then we should see two rows with values:
The values are different because America/Montreal is four hours behind UTC while Asia/Kolkata is five and a half hours ahead of UTC, and no adjustment for time zones was made. To repeat myself yet again, the stored values here represent only a date and a time-of-day, but without any context of time zone or offset-from-UTC, they do not represent a moment.
The confusion may be coming from the fact that some databases such a Postgres do adjust incoming values into UTC for values heading to a column of a different type, the TIMESTAMP WITH TIME ZONE type (note the WITH versus WITHOUT). Postgres and something other databases use any passed zone/offset info to adjust into UTC value, then discard the zone/offset info. So the type name is something of a misnomer, and you can think of it as TIMESTAMP WITH RESPECT FOR TIME ZONE.
If those same two users seen above at 2018-06-06T21:53Z were saving the current moment into a SQL-standard column of type TIMESTAMP WITH TIME ZONE, then the two rows would appear as:
The Z at the end is pronounced Zulu and means UTC.
What is it trying to solve by adjusting the value before saving it to the database?
Generally, the best practice in date-time handling is to work in UTC rather than in other zones/offsets.
When working as a programmer or sysadmin, forget about your own parochial time zone. Translating back-and-forth from your own zone to UTC or other zones will drive you batty. Think of UTC as the One True Time; all other zones are but mere variations.
The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
Instant instant = Instant.now() ; // Capture the current moment in UTC.
Store.
String sql = "INSERT INTO tbl ( event ) VALUES ( ? ) ;" ; // Writing a moment into a column of type `TIMESTAMP WTH TIME ZONE`.
myPreparedStatement.setObject( 1 , instant ) ; // As of JDBC 4.2 and later, we can directly exchange java.time objects with our database.
Retrieve.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
Present that moment in a particular time zone.
ZoneId z = ZoneId.of( "Asia/Kolkat" ) ;
ZonedDateTime zdt = instant.atZone( z ) ; // Same moment, same point on the timeline, different wall-clock 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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
