Search code examples
javajdbc

How to insert timestamp in UTC

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.

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

Solution

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