CODEWITHSUNDEEP
javamysqljdbctimestamp
LppEdd
LppEdd

Reputation: 21152

Java Date and MySQL timestamp time-zones

I'm editing a piece of code which basically does:

timestamp = new Date();

And then persist that timestamp variable in a TIMESTAMP MySQL table column.
However, while via debug I see the Date object displayed in the correct time-zone, GMT+1, when persisted on database it is a GMT time-zone, so an hour back.

Using the function CURRENT_TIMESTAMP returns a GMT+1 date.
The connection string is jdbc:mysql://..../db-name, without any parameter.

EDIT: Found this piece of code

preparedStatement.setTimestamp(2, new Timestamp(timestamp.getTime()));

What's happening?

Upvotes: 2

Views: 1399

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 340049

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?

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.

Upvotes: 1

Related Questions