MySql Applications in Different Time Zones Give Different Results

Question

Of course, the title is the expected result, but I'm not sure how to fix it. I have my production servers running MySql in MST. I test my application locally in EST.

We are using Joda time. Here is where the trouble comes:

DateTime nowTime = new DateTime();
DateTime endTime = myObject.getEndTime();
Duration duration = new Duration(nowTime, endTime);

if(duration.getStandardMinutes() < 15) {
    //do something time critical
}

Of course, this works fine when the application is being run in MST, but for my local application running EST, it's off by the time zone difference.

Does anyone know how to solve this? Assume that changing the default timezone on thy MySql server is not an option, since we have legacy applications that share it. Ideally we could just set the timezone to GMT so we could have UTC time.

Edit: Does anyone know if there's any way to avoid changing from UTC to current time zone on retrieval(see bold below from documentation)?

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

Or is the only option to convert this back to UTC programmatically after retrieval?

Or you can use `convert_tz()` in MySQL. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz — Barmar, Apr 18 '16 at 23:53
@Barmar Can you? I can't seem to find any documentation on how to do that. If I can, I believe that would be the solution here. — Matt, Apr 19 '16 at 00:24
@Barmar Per connection string would be better than modifying every sql stored proc to do this. — Matt, Apr 19 '16 at 00:26

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

Set the time zone for the connection with the query:

SET SESSION time_zone = 'UTC'

You can omit SESSION -- setting system variables defaults to the session variable. See SET Syntax documentation.

If you don't have time zone information configured into the database, see convert_tz returns null

You can also use a numeric time zone:

SET time_zone = '+00:00'

edited May 23 '17 at 11:45

Community

1
1

answered Apr 19 '16 at 00:28

Barmar

741,623
53
500
612

A little new to me. I'm using MySql Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using readline 5.1. Using: USE mysql; SELECT * FROM `time_zone_name`; returns no rows. And, of course, setting the session to 'EST' results in time zone not found. – Matt Apr 19 '16 at 00:37
See http://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465#14454465 for how to update the time zone table – Barmar Apr 19 '16 at 00:39
Thanks Barmar, this is a good lesson. However, I think I'd like to be able to set it with in the connection string. Is there no way to do that? Setting for the session is helpful in some cases, but would rather have the app itself using UTC via it's db connection. – Matt Apr 19 '16 at 00:48
Not as far as I know. – Barmar Apr 19 '16 at 00:49

score 0 · Answer 2 · answered Apr 18 '16 at 23:58

0

You could use the following code to get the UTC/GMT DateTime

System.out.println( "UTC/GMT date-time in ISO 8601 format: " + new org.joda.time.DateTime( org.joda.time.DateTimeZone.UTC ) );

answered Apr 18 '16 at 23:58

B. van den Hoek

54
1
4

MySql Applications in Different Time Zones Give Different Results

2 Answers2