JDBC: Insert timestamp in UTC

Question

My partial table definition (MySQL) looks as follows:

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ...       | ...      | ..   | ... | ...     | ...            |
| timestamp | datetime | YES  |     | NULL    |                |
| ...       | ...      | ..   | ... | ...     | ...            |
+-----------+----------+------+-----+---------+----------------+

I want to update the timestamp field using JDBC. The assumption is that the timestamp is in UTC.

In my Java code I am obtaining the timestamp field as follows:

Timestamp datetime = new Timestamp(new Date().getTime());
        

The documentation for the java.util.Date states that:
Although the Date class is intended to reflect coordinated universal time (UTC), it may not do so exactly, depending on the host environment of the Java Virtual Machine.

Unfortunately on my Windows 10 environment (java version "1.8.0_231"), the Date object reflects my local timezone. As a result the timestamp that gets added to the database is for the local timezone, not UTC.

---

Q) How can I obtain the timestamp in UTC, so that the correct value is added to the database?

Answer 1

tl;dr

You have the wrong data type in MySQL, and the wrong class in Java.

• Write to a column of type TIMESTAMP rather than DATETIME.
• Use java.time.OffsetDateTime rather than java.sql.Timestamp.

Example code snippet.

myPreparedStatement
.setObject( 
    … ,                       // Specify which placeholder `?` to fill-in.
    OffsetDateTime            // JDBC 4.2 and later requires a JDBC driver support exchanging `java.time.OffsetDateTime` objects with the database.
    .now( ZoneOffset.UTC )    // Capture the current moment as seen in UTC (an offset of zero hours-minutes-seconds). 
)

Wrong data type in MySQL

The assumption is that the timestamp is in UTC

Do you understand that DATETIME in MySQL is not in UTC? That type in MySQL has no concept of time zone nor offset-from-UTC.

If you are tracking moments, specific points on the timeline, you have the wrong data type in MySQL. You should be using TIMESTAMP to track moments in MySQL.

Wrong Java classes

You are using terrible date-time classes that were bundled with the earliest versions of Java. Those classes were supplanted by the modern java.time classes in Java 8 and later, as defined in JSR 310. Never use the java.sql.Timestamp class.

To get the current moment in UTC, use Instant.

Instant instant = Instant.now() ;

Define your column in MySQL as type TIMESTAMP.

You may be able to write an Instant via your JDBC driver. However, JDBC 4.2 requires support for OffsetDateTime but oddly omits required support for Instant. No matter, we can easily convert.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Write that to the database using your JDBC driver compliant with JDBC 4.2 or later, through a prepared statement.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Time zone problem eliminated

If you follow the code seen here you will have no time zone problems.

• None of this code depends on the JVM’s current default time zone.
• None of this code depends on the database server’s current default time zone.

---

---

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.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

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. Hibernate 5 & JPA 2.2 support java.time.

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

Answer 2

Q) How can I obtain the timestamp in UTC

Do it as follows:

ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Etc/UTC"));
Timestamp timestamp = Timestamp.valueOf(zdt.toLocalDateTime());

Notes:

1. Since you want to work with a timestamp, I recommend you change the field type to TIMESTAMP. Check this for more details.
2. Stop using the outdated java.util date-time API and switch to modern date-time API.