CODEWITHSUNDEEP
javamysqltimestamputctimezone-offset
Travis Fleenor
Travis Fleenor

Reputation: 167

Converting UTC time from MySQL to Local TimeZone

ANSWERED: This question is all over styackoverflow in different forms and I have been able to make good use of some of the answers. But at this point nothing seems to be working even previously answered questions. This problem seems like it should be simple enough and I am really at a loss here.

I have a project (an appointment calendar) where datetime needs to be stored in MySQL then displayed in the correct time for the timezone on the local machine wherever it is viewed from. To accomplish this I felt the easiest way would be to store the time in UTC. So wherever the appointment is created the start and end times are converted to UTC on the client and then stored in the database. So far so good. I was able to accomplish this with this code. The DateTimePermissive is particular to the picker I'm using

LocalDateTime st = start.getDateTimePermissive();  
ZonedDateTime stz = st.atZone(ZoneId.systemDefault());
ZonedDateTime stzUTC = stz.withZoneSameInstant(ZoneId.of("UTC"));
java.sql.Timestamp startTime = Timestamp.valueOf(stzUTC.toLocalDateTime());

LocalDateTime et = end.getDateTimePermissive();
ZonedDateTime etz = st.atZone(ZoneId.systemDefault());
ZonedDateTime etzUTC = etz.withZoneSameInstant(ZoneId.of("UTC"));
java.sql.Timestamp endTime = Timestamp.valueOf(etzUTC.toLocalDateTime());

Where I am running into trouble is coming back the other direction. I have tried all kinds of other ways of doing this and can't seem to be able to get any to work even though I felt my code is correct. Here is how I am trying to do it right now.

java.sql.Timestamp st = apptRecords.getTimestamp("start");
LocalDateTime stld = st.toLocalDateTime();
ZonedDateTime startTime = stld.atZone(ZoneId.systemDefault());
//String startTimeConverted = startTime.toString();

java.sql.Timestamp et = apptRecords.getTimestamp("end");
LocalDateTime etld = et.toLocalDateTime();
ZonedDateTime endTime = etld.atZone(ZoneId.systemDefault());
//String endTimeConverted = endTime.toString();

It seems like this should be working. I also tried creating a ZonedDateTime set to UTC and then setting that to the local time as well, thinking that maybe because java.sql.timestamp doesn't contain a timezone that might be necessary. That also did not work.

At this point I would be perfectly happy to store the timezone offset in a static variable somewhere as an int value and then just do ZonedDateTime.minus(ZoneOffsetNumber, minutes) or something like that in order to get the correct local time but I don't know how to find and store it as an int value.

It just keeps coming back in the UTC time. It shows the correct times zone name like American/Eastern but it still shows the time in UTC.

Should I be using atInstant? I couldn't get that to work either.

This is for a school project and I am not allowed to change any of the variable types in MySQL. These particular ones are datetime.

Anyone have experience doing this? It seems like storing on the server in UTC would be best practice for something like this. I am at a loss as to why it is so difficult to come back to the original timezone.

EDIT: I found the answer to this tanks to Abdelhafid's answer here: Is java.sql.Timestamp timezone specific?

By adding the connection properties he mentions to the connection properties

useTimezone=true
useLegacyDatetimeCode=false
serverTimezone=UTC

The times converted to the correct time regardless of what timezone I was signed in from.

So if anyone else is having an issue with this here is your answer. Set your connector like so

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "");
props.setProperty("useTimezone", "true");
props.setProperty("useLegacyDatetimeCode", "false");
props.setProperty("serverTimezone", "UTC");
Connection con = DriverManager.getConnection(conString, props);

Upvotes: 0

Views: 510

Answers (1)

Travis Fleenor
Travis Fleenor

Reputation: 167

I found the answer to this tanks to Abdelhafid's answer here: Is java.sql.Timestamp timezone specific?

By adding the connection properties he mentions to the connection properties

useTimezone=true
useLegacyDatetimeCode=false
serverTimezone=UTC

The times converted to the correct time regardless of what timezone I was signed in from.

So if anyone else is having an issue with this here is your answer. Set your connector like so

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "");
props.setProperty("useTimezone", "true");
props.setProperty("useLegacyDatetimeCode", "false");
props.setProperty("serverTimezone", "UTC");
Connection con = DriverManager.getConnection(conString, props);

Upvotes: 2

Related Questions