Timestamps and Time Zones in Java and MySQL

Question

I am developing a Java application with a MySQL database on a server located in a different time zone from mine, and I am trying to solve using DATETIME or TIMESTAMP in my database.

After reading questions such as Should I use the 'datetime' or 'timestamp' field? and the MySQL Documentation , I decided TIMESTAMP is better for me, as it converts the values ​​to UTC for storage and back to the current time zone for retrieval.

Also, as the Jesper user explains in this thread, java.util.Date objects are internally only a UTC timestamp (i.e., the number of milliseconds since Epoch) and when you do toString (), it displays according to your current hourly belt.

To me, this looks like a good practice: saving the time as UTC timestamps and then displaying them according to the current time zone.

I was going to do it like this, but then I found this from the Java documentation for prepared statements and was very confused:

void setTimestamp (int parameterIndex, Timestamp x, Calendar cal) throws an SQLException

Sets the assigned parameter to the given value java.sql.Timestamp using this Calendar object. The driver uses the Calendar object to create the SQL TIMESTAMP value, which then sends the database driver. Using the Calendar object, the driver can calculate based on the user’s time zone. If the object is a Calendar, the driver uses the default time zone that is used for the virtual machine on which the application is running.

Before that, I thought that timestamps were always in UTC. Why would anyone want to localize a timestamp instead of a localized representation of it? Isn't that too embarrassing for everyone?

How do these conversions work? If Java takes a UTC timestamp and converts it to an arbitrary timezone, how can it tell MySQL what timezone it is in?

Would MySQL not assume that this timestamp is in UTC, and then get the wrong localized value?

Answer 1

Time processing is a mess

The first paragraph in Teo's text is quite perceptive and believable: time processing in Java is a mess. The same goes for all the other languages ​​and development environments that I know of. Working with a date is very complicated and complicated, especially error-prone and disappointing, because we find it intuitive. But "intuitively" does not abbreviate it when it comes to data types, databases, serialization, localization, setting in time zones and all other formalities that come with computer programming.

Unfortunately, the computer industry has largely preferred to ignore this date issue problem. Just as Unicode took too much time to be invented with an obvious need, the industry also did not know what could solve the problem of date processing.

Do not rely on Count-Since-Epoch

But I must disagree with his conclusion. Working with count-since-epoch is not the best solution. Using count-since-epoch is inherently confusing and error prone and incompatible.

• People cannot read the long number and decrypt it as a date. So data validation and debugging is complicated, to say the least.
• Which “account” would you use? milliseconds used by java.util.Date and Joda-Time ? microseconds used by Postgres , MySQL, and other databases? nanoseconds used by new java.time package in Java 8?
• Which epoch would you use? The Unix epoch of early 1970 in UTC is general, but far from singular. Nearly two dozen eras were used by various computer systems.

We create numeric data types to do the math instead of using bits. We create string classes to handle the details of text processing, rather than bare octets. Therefore, we must create data types and classes to handle date and time values.

The early Java teams (both IBM and Taligent in front of them) made an attempt with java.util.Date and java. util.Calendar and related classes. Unfortunately, the attempt was inadequate. Although date-time is inherently confusing, these classes are even more confused.

Joda time

As far as I know, the Joda-Time project was the first project to take on a date in a thorough, and successful manner. However, the creators of Joda-Time were not completely satisfied. They continued to create the java.time package in Java 8 and extend this work with the threeten-extra project . Joda-Time and java.time use similar concepts, but differ from each other, each of which has some advantages.

Database Issues

In particular, the java.util.Date and .Calendar classes do not have values ​​only for a date without a time and time zone. And they lack time values ​​without a date and time zone. Before Java 8, the Java team added hacks known as java.sql.Date and java.sql.Time , which is a date-time value that masquerades as date-only. Both Joda-Time and java.time fix this by offering the LocalDate and LocalTime .

Another particular problem is that java.util.Date has a millisecond resolution, but databases often use microseconds or nanoseconds. In an ill-conceived attempt to overcome this inconsistency, the early Java team created another hack, the java.sql.Timestamp class. While technically a subclass of java.util.Date, it also tracks fractional seconds to nanosecond resolution. Therefore, when you convert and exit this type, you may lose or gain a smaller fractional second granularity without realizing this fact. Thus, this may mean that the values ​​you expect to be equal are not.

Another source of confusion is the SQL data type, TIMESTAMP WITH TIME ZONE . This name is incorrect because time zone information is not saved. Think of the name as TIMESTAMP WITH RESPECT FOR TIME ZONE , since any transmitted time zone offset information is used to convert the date and time value to UTC .

The java.time package with its resolution in nanoseconds has some features that allow better reporting of date data with the database.

I could write a lot more, but such information can be gathered from a StackOverflow search for words like joda, java.time, sql timestamp and JDBC.

An example of using Joda-Time with JDBC with Postgres . Joda-Time uses immutable objects for thread safety , so instead of changing the instance ("mutate"), we create a new instance based on the values ​​of the original.

 String sql = "SELECT now();"; … java.sql.Timestamp now = myResultSet.getTimestamp( 1 ); DateTime dateTimeUtc = new DateTime( now , DateTimeZone.UTC ); DateTime dateTimeMontréal = dateTimeUtc.withZone( DateTimeZone.forID( "America/Montreal" ) ); 

Focus in UTC

Before that, I thought that timestamps were always in UTC. Why would anyone want to localize a timestamp instead of a localized representation of it? Isn't that too embarrassing for everyone?

Really. The SQL standard defines TIMESTAMP WITHOUT TIME ZONE , which ignores and removes any included time zone data. I can not imagine the usefulness of this. This Postgres expert, David E. Wheeler, says he recommends always using TIMESTAMP WITH TIME ZONE . Wheeler refers to one narrow technical exception (separation) and even then says to convert all values ​​to UTC just before saving to the database.

The best practice is to work and store data in UTC when setting up localized time zones for presentation to the user. There may be times when you want to remember the original date and time in a localized time zone; if so, save this value in addition to converting to UTC.

Guide

The first steps to improve processing in date mode is to avoid java.util.Date and .Calendar using Joda-Time and / or java.time, focusing on UTC and studying the behavior of your specific JDBC driver and your specific databases (the databases are very different regarding data processing, despite the SQL standard).

Answer 2

Your question is a problem with a problem that seems huge these days. Both DB (through SQL) and the server itself (through programming languages ​​such as Java) offer a collection of ways to work with date and time. I would qualify the status quo as extremely non-standardized and a bit chaotic (personal opinion :)

My answer is incomplete, but I will explain why.

You're right, the Java Date (and Calendar) time is stored as milliseconds since the Unix Epoch (which is great). This happens not only in Java, but also in other programming languages. In my opinion, the ideal time storage architecture naturally arises from this: Unix Epoch - January 1, 1970, midnight, UTC. Therefore, if you decide to store the time in milliseconds since the release of Unix Epoch, you have many advantages:

• Clarity of architecture: the server side works with UTC, the client side shows time through its time zone
• Database simplicity: you save a number (milliseconds), not complex data structures like DateTimes
• programming efficiency: in most programming languages, you have date and time objects that can take milliseconds since the creation of Epoch (which, as you said, allows you to automatically convert to the time zone on the client side).

I believe that code and architecture are much simpler and more flexible when using this approach. I stopped trying to understand things like DateTime (or Timestamp), and only had to deal with them when I had to fix outdated code.