5

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?

Sandeep
  • 1,245
  • 1
  • 13
  • 33
  • 1
    Be careful with the description of the type. In MySQL `datetime` is a type where it will store the value without timezone information and you need to know which timezone to use to store/retrieve it, while `timestamp` will always stored the value in the database as UTC and you can be flexible with which timezone is use to store or retrieve the value. – DJ. Jun 28 '20 at 22:42
  • See also [Is java.sql.Timestamp timezone specific?](https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific) – Mark Rotteveel Jun 29 '20 at 11:59

3 Answers3

7

tl;dr

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

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.

Table of date-time types in Java (both legacy and modern) and in standard SQL

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?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 1
    JDBC only defines support for `java.time.OffsetDateTime` on `TIMESTAMP WITH TIME ZONE` columns. Have you verified if `getObject(.., OffsetDateTime.class)` works in this case? – Mark Rotteveel Jun 29 '20 at 12:00
  • @MarkRotteveel As I said three times in the Answer, the database in the Question is using the wrong data type in MySQL. – Basil Bourque Jun 29 '20 at 16:33
5

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.
Arvind Kumar Avinash
  • 71,965
  • 6
  • 74
  • 110
  • 1
    `OffsetDateTime` is more appropriate here than `ZonedDateTime`. And `OffsetDateTime` can be directly sent to the database, so no need for the troubled legacy class `java.sql.Timestamp`. – Basil Bourque Jun 29 '20 at 06:04
  • 1
    Saved my day even after getting time in utc by Instant.now wasn't getting way to put that in SQLQuery – gladiator Oct 25 '21 at 09:14
1

You can let MySQL to insert a timestamp using this column definition

CREATE TABLE SOME_TABLE_NAME (
     SOME_COLUMN_NAME TIMESTAMP default CURRENT_TIMESTAMP
);
v.ladynev
  • 19,275
  • 8
  • 46
  • 67