4

I have a Joda DateTime object representing a UTC time, and wish to store it in a Timestamp field in a MySql table.

I have the following code:

String ztime = "2013-10-07T08:00:00Z";  

DateTimeFormatter parser = ISODateTimeFormat.dateTimeParser();
DateTime dt = parser.parseDateTime(ztime).withZone(DateTimeZone.UTC);

PreparedStatement stmt = con.prepareStatement("insert into time_test (time) values (?)");
stmt.setTimestamp(1, Timestamp(dt.getMillis()));
stmt.execute();

However, when I look in the database, the time that gets store is out by the difference of my database's timezone from UTC. e.g. when my database is running in UTC+1, and run the above code to save "08:00Z", in the database the Timestamp shows as 09:00.

DateTime's getMillis method says " Gets the milliseconds of the datetime instant from the Java epoch of 1970-01-01T00:00:00Z." and MySql's Timestamp says: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.", so I presume it's the MySql conversion that's causing the issue, because the millis it's being initialized with is relative to a fixed UTC time, so it has no need to convert from current time zone to UTC.

My code to read the data back out into a DateTime works fine, and I get the value out that I put in, but I also need this to work with some 3rd-party code over which I have no control, which expects the Timestamp to be in the correct UTC time.

How do I get the Timestamp field in the database to match my original UTC date/time ?

Dave W
  • 1,061
  • 3
  • 16
  • 29

1 Answers1

0

tl;dr

Use java.time classes that supplant Joda-Time.

myPreparedStatement.setObject( 
    … , 
    Instant.parse( "2013-10-07T08:00:00Z" )  
)

Retrieve.

myResultSet.getObject( 
    … ,
    Instant.class 
)

java.time

The Joda-Time project is now in maintenance-mode, recommending migration to its successor, the java.time classes built into Java 8 and later. Both are led by the same man, Stephen Colebourne. You'll find many of the same concepts in play, so fairly easy to migrate.

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).

Your input string happens to be in standard ISO 8601 format. The java.time classes use these standard formats by default when parsing/generating strings. So no need to specify a formatting pattern.

String input = "2013-10-07T08:00:00Z" ;     // Standard ISO 8601 format.
Instant instant = Instant.parse( input ) ;  // Parses standard ISO 8601 format by default.

The Instant class replaces both java.util.Date and java.sql.Timestamp. As of JDBC 4.2 and later, you can directly exchange java.time objects with the database.

myPreparedStatement.setObject( … , instant ) ;

And retrieval.

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

The TIMESTAMP type in MySQL seems to be akin to the SQL-standard TIMESTAMP WITH TIME ZONE type. So the code above should work appropriately.

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154