Timestamp and time zone conversion in Java and MySQL

I am developing a Java application with MySQL database on a server located in a different time zone from me. I am trying to use datetime or timestamp in my database

Should I use field 'datetime' or 'timestamp'? After problems such as MySQL documentation, I think timestamp is better for me because it converts the value into UTC for storage, and then returns it to the current time zone for retrieval

In addition, as user Jesper explained in this thread, Java util. Internally, the date object is only a UTC timestamp (that is, the number of milliseconds since epoch). When you execute tostring(), it will be displayed according to your current time zone

To me, this seems like a good practice: store dates and times as UTC timestamps and display them according to the current time zone

That's what I did, but later I found that it was a statement prepared from Java documentation and was very confused:

Before that, I thought timestamp was always in UTC convention Why do people want a localized timestamp instead of its localized representation? Isn't this confusing for everyone?

How do these transformations work? If Java takes UTC timestamp and converts it to any time zone, how does it tell MySQL which time zone it is in?

Will MySQL not assume that this timestamp is UTC and then retrieve incorrect localized values?

Solution

Date time processing is a mess

The first paragraph in answer by Teo is very insightful and correct: date and time processing in Java is a mess The same applies to all other languages & development environments I know Date and time work is difficult and tricky, especially error prone and frustrating, because we intuitively think it is date and time However, "intuitively" does not cut down on data types, databases, serialization, localization, cross - time zone adjustment, and all the other formalities brought about by computer programming

Unfortunately, the computer industry has largely chosen to ignore the problem of date work As in the case of obvious need, Unicode takes too long to invent, so the industry is also trying to solve the problem of date and time processing

Do not rely on count since epoch

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

>Humans cannot read long numbers and interpret them as dates and times Therefore, at least, verifying data and debugging become complex. > What "quantity" would you use? java. util. Date and joda time use milliseconds? Microseconds uses microseconds, MySQL and other databases? New Java Nanoseconds used by time package in Java 8? > Which epoch would you use? UNIX epoch in UTC in early 1970 is common, but far from singular Almost two dozen epochs have been used by various computer systems

Instead of using bits, we create numeric data types for mathematical operations We create string classes to handle text rather than bare octet details We should also create data types and classes to handle date - time values

Early Java teams (and IBM & taligent before them) tried to use Java util. Date and Java util. Calendar and related classes Unfortunately, this attempt is not enough Although dates and times are confusing in nature, these classes add more confusion

Jorda time

As far as I know, joda time project is the first project to undertake date and time in a thorough, competent and successful manner Even so, the creators of joda time are not entirely satisfied They continue to create Java. Net in Java 8 Time package and extended this work with three extra project Joda time and Java Time shares similar concepts, but each has some advantages

Database problems

Specifically, Java util. Date& . The calendar class is missing a date only value and has no time and time zone And they lack time - only values without dates and time zones Before Java 8, the Java team added something called Java sql. Date and Java sql. Hacks of time class, which is a date time value disguised as date only Joda time and Java Time corrects it by providing localdate and Localtime classes

Another specific problem is Java util. The resolution of date is milliseconds, but databases often use microseconds or nanoseconds In an unwise attempt to bridge this gap, the early Java team created another hack, Java sql. Timestamp class Although it is technically Java util. Date subclass, but it also tracks resolution from decimal seconds to nanoseconds Therefore, when converting to this type, you may lose or gain finer fractional second granularity without realizing this fact This may mean that you expect equal values not

Another reason for confusion is the SQL data type, timestamp with time zone The name is not used properly because the time zone information is not stored Treat the name as timestamp with expect for time zone because any time zone offset information passed is used to convert the date time value to UTC

Java. Net with nanosecond resolution The time package has some specific functions to better communicate date and time data with the database

I can write more, but I can get this information by searching stackoverflow, such as joda, Java Time, SQL timestamp and JDBC

Examples of using joda time, JDBC and Postgres Joda time uses immutable objects to represent thread safety Therefore, instead of changing the instance ("mutate"), we create a new instance based on the original value

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 on UTC

exactly. The SQL standard defines timestamp with time zone, which ignores and deletes any contained time zone data I can't imagine its usefulness David E. Wheeler, the Postgres expert, said as much in recommending, always uses timestamp with time zone Wheeler cites a narrow technical exception (partition), and even before saving to the database, he says he converts all values to UTC

The best practice is to work and store data in UTC format and adjust it to a localized time zone for presentation to users Sometimes you may want to remember the original date time data in its localized time zone; If so, save the value in addition to converting to UTC

policy

The first step to better date and time processing is to avoid Java util. Date& . Calendar, using joda time and / or Java Time, focus on UTC, and learn the behavior of specific JDBC drivers and specific databases (databases vary greatly in date and time processing, although there are SQL standards)

Mysql

Warning: I don't use MySQL (I'm a Postgres type person)

According to version 8 documentation, datetime and timestamp differ in that the first type lacks any time zone concept or is offset from UTC The second adjusts the value to UTC using any indication of the time zone or the UTC offset indication accompanying the input, then stores it and discards the area / offset information

So these two types seem to be similar to the standard SQL types:

>Mysqldatetime ≈ SQL standard timestamp without time zone > mysqltimestamp ≈ SQL standard timestamp with time zone

For MySQL datetime, use the Java class localdatetime Like this data type, this class intentionally lacks any concept of time zone or offset from UTC Use this type and class for:

>When you refer to any area or all areas, such as "Christmas begins at the first moment on December 25, 2018" This can translate into different times in different places, because the East gets up earlier than the West. > Politicians around the world have shown a tendency to change the deviation of time zone when arranging appointments or events in the future In this usage, you must apply the time zone at runtime to dynamically calculate (but not store) the time displayed on the calendar In this way, even if politicians redefine the clock as a few minutes / hour in advance, the dental appointment at 15:00 in 8 months is still 15:00

For MySQL timestamp, please use the Java class instant, as shown above Use this type and class for specific points on the timeline

JDBC 4.2

Starting with JDBC 4.2 and later, we can directly exchange java with the database Time object Use the GetObject & setobject method

myPreparedStatement.setObject( …,Instant.Now() ) ;

Recovery

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

You can then adjust the UTC value in instant to a specific time zone for presentation to users

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
zoneddatetime zdt = instant.atZone( z ) ;

About Java time

java. The time framework is built into Java 8 and later These courses replace the troublesome old legacy date time courses, such as Java util. Date, calendar and & simpledateformat

The joda time project is now maintenance mode. It is recommended to migrate to Java Time course

For more information, see Oracle tutorial And search stack overflow for many examples and instructions The specification is JSR 310

You can exchange Java. Net directly with the database Time object Use a jdbc driver that conforms to JDBC 4.2 or later No strings, no Java sql.* Class

Where to get Java Time class?

>Java se 8, Java se 9, Java se 10 and later

>Built in. > Some standard Java APIs with bundled implementations. > Java 9 has added some small functions and fixes

>Java se 6 and Java se 7

>Most Java The time function was reverse ported to Java 6& July 7, 2007

> Android

>Updated version of Android bundled Java Implementation of time class. > For early Android (< 26), the threetenabp project was adapted to threeten backup (as described above) See how to use threetenabp

The threeten extra project extends Java. Net with other classes time. The project is likely to be added to Java in the future Time's proving ground You can find some useful classes here, such as interval, yearweek, yearquarter and more