Saving java.sql.Timestamp to mysql datetime column changes the time

Question

I'm working on a Spring Boot REST API that uses MySQL database. I'm trying to save a date (with time) received from client app "as is" which is in UTC time. Basically, it's already agreed upon that the dates will be sent back and forth in UTC and the conversion to appropriate timezone would be done on the client, so I already have UTC time in JSON and I'm trying to save it to a DATETIME column in MySQL.

I chose DATETIME because from MySQL reference it says that TIMESTAMP converts values, while DATETIME doesn't, which is exactly what I need.

I've created all the required entities to map my tables and again, from reference, I've seen that DATETIME maps to java.sql.Timestamp so that's what I used as type.

I'm using JpaRepository and calling it's save method to save my entity. What happens is: when I save dates to MySQL they get converted to UTC (I assume, because I'm in UTC+1 and the dates are one hour earlier than what I inserted).

I've read several answers on SO and I've tried using the following attributes in my jdbc connection string (as suggested here): noDatetimeStringSync=true, useLegacyDatetimeCode=false, sessionVariables=time_zone='-00:00' but none of them worked.

I don't know if maybe spring does something that's messing with this?

This is part of the JSON I receive in request:

{
 "dateFrom": "2019-12-01 16:00:00",
 "dateTo": "2019-12-01 16:30:00"
}

My entity has these properties:

import java.sql.Timestamp;
...
private Timestamp dateFrom;
private Timestamp dateTo;

Table columns in db:

date_from   datetime
date_to     datetime

The entity has the right values inside it when I convert String to Timestamp and invoke save() method. So something between JpaRepository and the db itself messes with the dates. UPDATE: The Timestamp actually wasn't the right value, it added ZoneInfo with ID="Europe/Prague" and zoneOffset=3600000 while converting from String.

What I end up with is 2019-12-01 15:00:00 and 2019-12-01 15:30:00 in db.

What I would like is to store these dates exactly the way I received them. I even thought about switching to VARCHAR because I got so frustrated, but I don't want to do that because I need to perform queries based on dates etc.

Is there any way I can achieve what I'm looking for? It seemed pretty straight-forward at first, but now it's literally driving me insane.

[Additional info if required] I'm using:

• MySQL 5.7.27
• Spring Boot (starter-parent) 2.2.0.RELEASE
• mysql-connector-java

EDIT:

I might be going about this the wrong way, so if you could suggest how I could do this differently that would be awesome. The point is: my app needs to work for users in different timezones and they need to communicate with each other through dates. So if one user in timezone Europe/Prague says "let's talk tomorrow at 5pm" to another user in America/Chicago, I need to store this information in a way that can be translated for user in America in their local time (but also for any other user in any other timezone). This is why I opted for storing dates in UTC and then converting them on the client side to the user's local time. But apparently I'm misunderstanding something about how it all works.

Answer 1

Thanks to deHaar and everyone else who contributed in the comments, I finally got it to work the way I intended it, so I'm going to post a summarized answer in case someone else might need it.

The key was using a standardized date string in JSON request/response, along with proper Java 8 DateTime objects. What I was originally sending simply wasn't enough.

So according to ISO-8601 date and time formats:

Times are expressed in UTC (Coordinated Universal Time), with a special UTC designator ("Z").

Note that the "T" appears literally in the string, to indicate the beginning of the time element, as specified in ISO 8601.

Meaning, my API should communicate only in these standardized UTC date strings and no custom date string stuff, e.g. :

{
    "dateFrom": "2019-12-01T16:00:00Z",
    "dateTo": "2019-12-01T16:30:00Z"
}

Another thing I did, as mentioned in the comments, was set the db timezone to UTC and leave nothing to chance. Since I use spring boot there were two ways to do this:

1. In application.properties set property: spring.jpa.properties.hibernate.jdbc.time_zone=UTC

2. Or in the jdbc connection string: serverTimezone=UTC

I've also added useLegacyDatetimeCode=false parameter to the string above, because I've read that without it, the serverTimezone param has no effect (someone is free to correct me if I'm wrong about this).

Now, there is some debate whether the java.sql.Timestamp has timezone information or not. I went through SO to read more about it and it turns out that previous versions (before Java 8) indeed didn't have timezone information (like here for example). However, as I clearly saw in the debugger, Timestamp object has zone info stored separately, but it does have it.

According to Mark Rotteveel from the comments

a java.sql.Timestamp by specification represents time in the default JVM timezone

which makes sense, cause when I changed JVM timezone, the Timestamp values changed as well.

Now, to tackle this issue, I've seen lots of suggestions to set the default timezone to UTC (cause if it didn't have it set, it would naturally fallback to JVM's). I tried this as well by using the following code

System.setProperty("user.timezone", "UTC");

and it worked because it was now converting the db values to UTC, but what I didn't like about this approach is that it changed everything to UTC time (duh), including my logs. It just didn't feel natural to do it "forcefully" like this.

Another discovery that was very meaningful to me is that everyone kept saying to switch completely to java.time and its classes, which made like 80% of answers on SO pretty deprecated and useless, since they suggest using old classes to do stuff like parsing, conversion etc. (Still not sure if I'm able to completely ditch java.sql.Timestamp as well, since for now the docs say that this is the type to map to from datetime format in DB, but for now I'll leave it).

Finally, what I did was create a little util class to help me with the conversions I need.

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class DateTimeUtil {

    public static Timestamp getTimestamp(String utcDateTime) {
        LocalDateTime localDateTime = LocalDateTime.parse(utcDateTime, DateTimeFormatter.ISO_DATE_TIME);
        return Timestamp.from(localDateTime.atZone(ZoneId.of("UTC")).toInstant());
    }

    public static String getUTCString(Timestamp dbTimestamp) {
        return dbTimestamp.toInstant().atZone(ZoneId.of("UTC")).toString();
    }

}

The first function is used for parsing string date from JSON request.

I opted for LocalDateTime instead of ZonedDateTime because of this info from java.time doc:

Where possible, it is recommended to use a simpler class without a time-zone. The widespread use of time-zones tends to add considerable complexity to an application.

(EDIT: The code was updated, using LocalDateTime was incorrect exactly because it strips timezone of UTC and then behaves exactly like JVM situation did. Sorry, I tested this wrongly)

The second function is used for sending response in JSON. While the values were being stored the way they should in DB, when I retrieved them back, they got converted to my local timezone (since JVM is in a different timezone). That's why it needed to be said "hey, this value is UTC timezone, show it like that, not in my local timezone".

So now, before saving my entity to DB, I set its timestamp with getTimestamp() which saves it as UTC, and when I retrieve the value and prepare my DTO fro response, I use getUTCString() which also makes it ISO formatted UTC, all the while the DB acts like it's located in UTC so it can't add something of its own to the mix AND the communication between the client and the API is standardized and everyone knows exactly what to expect.

Answer 2

If you want to make sure you are dealing with UTC times, then you should store them as such in the database. You can specifiy a zone or an offset in Java:

public ZonedDateTime utcDateTimeFrom(Timestamp timestamp) {
    return timestamp.toLocalDateTime().atZone(ZoneId.of("UTC"));
}

public Timestamp toUtcTimestamp(LocalDateTime localDateTime) {
    return Timestamp.from(localDateTime.atZone(ZoneId.of("UTC")).toInstant());
}

Try the methods or write similar ones (there is OffsetDateTime, too), but I think the date time in your JSON response/request is not sufficient, you will have to send the time zone or the offset from client side, too, otherwise you may get into trouble with the time zones.

Answer 3

Application's timezone is used while converting Database's DateTime to java.sql.Timestamp. You would need to pass environment variables or set it in application.

-Duser.timezone=America/Chicago

or

TimeZone.setDefault(TimeZone.getTimeZone("America/Chicago"));

Edit:

I think you are missing a very important point.

java.sql.Timestamp is nothing but a long value which is the number of milliseconds elapsed since 1971. This is independent of time zones, they are needed only when you want to get string representation.

When you create object of java.sql.Timestamp from some string representation 2019-12-01 16:00:00 there itself your value would have got shifted by using your server timezone.

Coming to your second question.

For example - 1573251160 is the time when i am writing this answer. This value will mean same Instant in time in all timezones.

Now this needs to be converted to DateTime Column to store in database which is a timezone dependent field. Without knowing the timezone you simply cannot choose a time as time will be different in different timezones. Without knowing the timezone its impossible to convert. For example : Same value will get converted to:

1. Fri, 08 Nov 2019 22:12:40 GMT
2. Fri, 09 Nov 2019 3:42:40 AM GMT+05:30

The conversion will depend on which timezone your server runs on. If you are always expecting your values to get stored in UTC it seems reasonable to run your server on UTC.