Handling MySQL Datetimes and Timestamps in Java

Handling MySQL datetimes and timestamps in Java

In Java side, the date is usually represented by the (poorly designed, but that aside) java.util.Date. It is basically backed by the Epoch time in flavor of a long, also known as a timestamp. It contains information about both the date and time parts. In Java, the precision is in milliseconds.

In SQL side, there are several standard date and time types, DATE, TIME and TIMESTAMP (at some DB's also called DATETIME), which are represented in JDBC as java.sql.Date, java.sql.Time and java.sql.Timestamp, all subclasses of java.util.Date. The precision is DB dependent, often in milliseconds like Java, but it can also be in seconds.

In contrary to java.util.Date, the java.sql.Date contains only information about the date part (year, month, day). The Time contains only information about the time part (hours, minutes, seconds) and the Timestamp contains information about the both parts, like as java.util.Date does.

The normal practice to store a timestamp in the DB (thus, java.util.Date in Java side and java.sql.Timestamp in JDBC side) is to use PreparedStatement#setTimestamp().

java.util.Date date = getItSomehow();
Timestamp timestamp = new Timestamp(date.getTime());
preparedStatement = connection.prepareStatement("SELECT * FROM tbl WHERE ts > ?");
preparedStatement.setTimestamp(1, timestamp);

The normal practice to obtain a timestamp from the DB is to use ResultSet#getTimestamp().

Timestamp timestamp = resultSet.getTimestamp("ts");
java.util.Date date = timestamp; // You can just upcast.

How to store Java Date to Mysql datetime with JPA

see in the link :

http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion

The following code just solved the problem:

java.util.Date dt = new java.util.Date();

java.text.SimpleDateFormat sdf = 
     new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currentTime = sdf.format(dt);

This 'currentTime' was inserted into the column whose type was DateTime and it was successful.

Java Date and MySQL timestamp time-zones

tl;dr

myPreparedStatement
.setObject(  
    … ,                                   // Specify which placeholder `?` in your SQL statement. 
    OffsetDateTime.now( ZoneOffset.UTC )  // Capture the current moment as seen in the wall-clock time of UTC (an offset-from-UTC of zero).
) ;

Avoid legacy date-time classes

You are using terrible date-time classes that were supplanted years ago by the java.time classes.

Never use Date or Timestamp.

UTC

Capture the current moment, in UTC. Most databases store a moment in UTC. And generally you should do most of your business logic, debugging, logging, storage, and data exchange in UTC.

OffsetDateTime

Represent a moment with an offset-from-UTC using the aptly named OffsetDateTime class.

We want UTC itself, or an offset of zero. We can use a constant for that, ZoneOffset.UTC.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ) ;

JDBC 4.2

As of JDBC 4.2 we can directly exchange java.time objects with the database.

To save this moment to a column of a data type akin to the SQL-standard TIMESTAMP WITH TIME ZONE:

myPreparedStatement.setObject( … , odt ) ;

Retrieval:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

ZonedDateTime

To present this retrieved moment to the user, you may want to adjust into the user’s expected/desired time zone.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Never rely on default time zone

Notice in the code above that we always specified the desired/expected offset or zone.

If you do not specify, an offset or zone is silently implicitly applied. Better to specify your intentions explicitly as the current default of your JVM, database, and host OS are all out of your hands as a programmer. Which means code relying on the default will vary in behavior at runtime.

Java 6 & 7

However I'm still forced to deal with Java 6

The same man, Stephen Colebourne, who leads JSR 310 and the java.time implementation, as well as the famous Joda-Time project, also leads another project, ThreeTen-Backport. Most of the java.time functionality is back-ported to Java 6 & 7 in this library, with nearly identical API.

So do all your work in back-port classes. Then, at the last moment, convert to/from java.sql.Timestamp via the DateTimeUtils class.

Those conversion methods mostly use Instant objects. An Instant is a moment in UTC, always in UTC. You can adjust from your OffsetDateTime to UTC by extracting an Instant. The Instant class is the basic building-block class in java.time, with OffsetDateDate having more flexibility such as alternate formatting patterns when generating a string. But both Instant and OffsetDateTime represent a moment, a point on the timeline.

Instant instant = odt.toInstant() ;  
java.sql.Timestamp ts = org.threeten.bp.DateTimeUtils.toSqlTimestamp( instant ) ;

Going the other direction, retrieving a Timestamp from your database, then immediately converting to a Instant.

java.sql.Timestamp ts = myResultSet.getTimestamp( … ) ;
Instant instant = org.threeten.bp.DateTimeUtils.toInstant( ts ) ;

---

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, Java SE 11, and later - 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
  
  • Most 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.

MySQL DATETIME and TIMESTAMP to java.sql.Timestamp to ZonedDateTime

Let's address each question you have. First:

Is there a zone-type conversion taking place (when going through MySQL-connector 5.1.37) from MySQL's DATETIME to java.sql.Timestamp (such as to apply the client system zone)?

First off, I presume that you are using the getTimestamp(int) method from the connector. I could not find an official source that showed me an enlightening answer; however, there was this question in which the answer stated:

When you call getTimestamp(), MySQL JDBC driver converts the time from GMT into default timezone if the type is timestamp. It performs no such conversion for other types.

However, in this version of the method, it uses an underlying Calendar to convert the Timestamp to the TimeZone specified, if the underlying database doesn't store time zone information. This may be the solution to your second question, as long as you knew the time zone at which the value was stored (which you do). But if it is not, it seems that with the first method there is no conversion taking place, at least when it retrieves the DATETIME. Speaking about your second question:

But I would like to work with ZonedDateTime, going back and forth to the database stored as DATETIME.

It makes me think that there is a way to do this as long as you knew which time zone you are converting from. As we have previously stated, you and your clients are only working with one ZoneId, which is totally fine. However, this answer is provided to work with more time zones. Multiple ZoneId's can be achieved if you were to store the ZoneId of the connection in the database; retrieving it as well as the DATETIME and finally processing these values into a ZonedDateTime. You could store the ZoneIds into the database using the ID's of the ZoneId class (if you wanted to).

Timestamp t = resultSet.getTimestamp(timestampColumnId);
ZoneId zoneId = ZoneId.of(resultSet.getString(zoneColumnId), ZoneId.SHORT_IDS);
ZonedDateTime d = ZonedDateTime.ofInstant(t.toInstant(), zoneId);

Or, you could just store the DATETIME as a TIMESTAMP in the database as ZZ Coder suggests in his answer stated above. But, you could just use the ZoneId you have hard-coded as such:

Timestamp t = resultSet.getTimestamp(timestampColumnId);
ZonedDateTime d = ZonedDateTime.ofInstant(t.toInstant(), zoneId);

EDIT
Looking at the source code, on get or set calls using the getTimestamp(int, Calendar) or the setTimestamp(int, Timestamp, Calendar) function, the timezone of the Calendar is used. However, in some cases with TIMESTAMP, when a Calendar is not used, the JDBC then uses the time zone of the server. And according to the original poster, it worked (see comment below).

Timestamps and time zone conversions in Java and MySQL

Date-Time Handling Is A Mess

The first paragraph in the answer by Teo is quite insightful and correct: Date-time handling in Java is a mess. Ditto for all other languages & development environments that I know of. Date-time work is difficult and tricky, especially error-prone and frustrating because we think it of date-time intuitively. But "intuitively" does not cut it when it comes to data types, databases, serialization, localization, adjusting across time zones, and all the other formalities that come with computer programming.

Unfortunately, the computer industry basically chose to ignore this problem of date-time work. Just as Unicode took too long to be invented given the obvious need, so too has the industry kicked the can down the road on solving the problem of date-time handling.

Do Not Rely On Count-Since-Epoch

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

• Humans cannot read a long number and decipher that as a date-time. So verifying data and debugging becomes complicated, to say the least.
• What "count" would you use? The milliseconds used by java.util.Date and by Joda-Time? The microseconds used by Postgres, MySQL, and other databases? The nanoseconds used by the new java.time package in Java 8?
• Which epoch would you use? The Unix epoch of the beginning of 1970 in UTC is common, but far from singular. Almost two dozen epochs have been used by various computer systems.

We create numeric data types for doing math rather than using bits. We create string classes to handle the nitty-gritty details of handling text rather than bare octets. So too we should create data-types and classes to handle date-time values.

The early Java teams (and IBM & Taligent before them) made an attempt with the java.util.Date and java.util.Calendar and related classes. Unfortunately, the attempt was inadequate. While date-time is inherently confusing, these classes have added even more confusion.

Joda-Time

As far as I know, the Joda-Time project was the first project to take on date-time in a thorough, competent, and successful manner. Even so, the creators of Joda-Time were not entirely satisfied. They went on to create the java.time package in Java 8, and extend that work with the threeten-extra project. Joda-Time and java.time share similar concepts but are distinct, each having some advantages.

Database Problems

Specifically, the java.util.Date & .Calendar classes lack date-only values without time-of-day and time zone. And they lack time-only values without date and time zone. Before Java 8, the Java team added the hacks known as the java.sql.Date and java.sql.Time classes which is a date-time value masquerading as a date-only. Both Joda-Time and java.time rectify that by offering LocalDate and LocalTime classes.

Another specific problem is that java.util.Date has a resolution of milliseconds, but databases frequently use microseconds or nanoseconds. In an ill-advised attempt to bridge this disparity, the early Java team created another hack, the java.sql.Timestamp class. While technically a java.util.Date subclass, it also tracks the fractional seconds to nanosecond resolution. So when converting in and out of this type you may losing or gaining the finer fractional seconds granularity without being conscious of that fact. So that might mean that values you expect to be equal are not.

Another source of confusion is the SQL data type, TIMESTAMP WITH TIME ZONE. That name is a misnomer as the time zone info is not stored. Think of the name as TIMESTAMP WITH RESPECT FOR TIME ZONE as any passed time zone offset info is used in converting the date-time value to UTC.

The java.time package with its nanosecond resolution has some specific features to better communicate date-time data with a database.

I could write much more, but such information can be gleaned from searching StackOverflow for words such as joda, java.time, sql timestamp, and JDBC.

Example using Joda-Time with JDBC with Postgres. Joda-Time uses immutable objects for thread-safety. So rather than alter an instance ("mutate"), we create a fresh 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 On UTC

Before this, I thought timestamps were by convention always in UTC. Why on earth would anyone want a localized timestamp instead of a localized representation of it? Wouldn't that be very confusing for everyone?

Indeed. The SQL standard defines a TIMESTAMP WITHOUT TIME ZONE which ignores and strips away any included time zone data. I cannot imagine the usefulness of that. This Postgres expert, David E. Wheeler, says as much in recommending always using TIMESTAMP WITH TIME ZONE. Wheeler cites one narrow technical exception (partitioning) and even then says to convert all the values to UTC yourself before saving to the database.

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

Guidelines

The first steps to better date-time handling are avoiding java.util.Date & .Calendar, using Joda-Time and/or java.time, focusing on UTC, and learning the behavior of your specific JDBC driver and your specific database (databases vary widely in their date-time handling despite the SQL standard).

MySQL

Caveat: I don’t use MySQL (I'm a Postgres kind of guy).

According to the version 8 documentation, the two types DATETIME and TIMESTAMP differ in that the first one lacks any concept of time zone or offset-from-UTC. The second one uses any indication of time zone or offset-from-UTC accompanying an input to adjust that value to UTC, then stores it, and discards the zone/offset info.

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

• MySQL DATETIME ≈ SQL-standard TIMESTAMP WITHOUT TIME ZONE
• MySQL TIMESTAMP ≈ SQL-standard TIMESTAMP WITH TIME ZONE

For MySQL DATETIME, use the Java class LocalDateTime. That class, like that data type, purposely lacks any concept of time zone or offset-from-UTC. Use this type and class for either:

• When you mean any zone or all zones, such as “Christmas starts on first moment of December 25, 2018”. That translates to different moments in different places as a new day dawns earlier in the east than in the west.
• When scheduling appointments or events far enough out in the future that politicians may change the offset of the time zone, for which politicians around the world have shown a proclivity. In this usage, you must at runtime apply a time zone to dynamically calculate, but not store, a moment for display on a calendar. That way, a 15:00 dental appointment in 8 months remains at 15:00 even if politicians redefine the clock to be minutes/hours ahead or behind.

For MySQL TIMESTAMP, use the Java class Instant, as shown above. Use this type and class for moments, specific point on the timeline.

JDBC 4.2

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database. Use getObject & setObject methods.

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

Retrieval.

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

The JDBC 4.2 specification requires a driver to support OffsetDateTime but strangely does not require support for the more common types Instant and ZonedDateTime. But converting between types is quite easy.

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

You can then adjust that UTC value in Instant to a specific time zone for presentation to a user.

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

---

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.

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

---

Related Topics