Why does JDBC adjust the time to default timezone when it is saving a date?

Question

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.

1. What's exactly wrong with NOT adjusting and saving the value (UTC) as it is?
2. What is it trying to solve by adjusting the value before saving it 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?

Makalu

@MattJohnson, what are usually discussed are the whats and hows and not the whys. – Makalu

Say I create a timestamp with 2017-03-14 07:58:00 and save it to the database and look at the value using a non-JDBC tool and then I see e.g. 2017-03-14 04:58:00 - what's correct about that? – Burglarious

@a_horse_with_no_name, that's what exactly what JDBC does, it adjusts 2017-03-14 07:58:00 to 2017-03-14 04:58:00 and we're asking the same question. What's correct about that? – Makalu

No, it's not what JDBC does. When I create a Java date (precisely a java.sql.Timestamp) with 2017-03-14 07:58:00 it is saved as that. – Burglarious

@a_horse_with_no_name, yes it does, if your timezone is not UTC, or if you have set your timezone as UTC, or your timezone has an offset from UTC. Otherwise, there will be no need to set the timezone to UTC just to force the JDBC not to adjust anything. – Makalu

@a_horse_with_no_name, demo: #34173133 – Makalu

The Date object value is not stored in UTC. It is stored as the "number of milliseconds since the standard base time known as "the epoch"", where base time means "an agreed on instant in time". – Disharmonious

@DavidBalažic, technically speaking, you are correct. Value is not stored in UTC as it does not have timezone information. But it also does not have time offset which makes it essentially equivalent to UTC. – Makalu

Answer 1

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.

java.time

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:

• The zone or offset is unknown.
  This is bad. This is faulty data. Analogous to having a price/cost without knowing the currency. You should be rejecting such data, not storing it.
• The intention is “everywhere”, as in, every time zone.
  For example, a corporate policy that states “All our factories will break for lunch at 12:30" means the factory in Delhi will break hours before the factory in Düsseldorf which breaks hours before the factory in Detroit.
• A specific moment in the future is intended, but we are afraid of politicians redefining the time zone.
  Governments change the rules of their time zones with surprising frequency and with surprisingly little warning or even no warning at all. So if you want to book an appointment at 3 PM on a certain date, and you really mean 3 PM regardless of any crazy decision a government might make in the interim, then store a 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:

• 2018-06-06T17:53 (notice the date is “yesterday”)
• 2018-06-07T03:23 (notice the date is “tomorrow”)

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:

• 2018-06-06T21:53Z
• 2018-06-06T21:53Z

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.

---

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?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

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.

Squat

JDBC drivers actually do convert timestamps when writing to TIMESTAMP WITHOUT TIME ZONE fields. This seems to be required by the JDBC specification - see e.g. this answer to a Github issue for the PostgreSQL JDBC driver – Euphemism

Here's the code in the PostgreSQL JDBC driver that is used when you call setTimestamp(int, java.sql.Timestamp) (so without a Calendar). It converts the timestamp to a string using a calendar based on the JVMs default timezone . – Euphemism