CODEWITHSUNDEEP
javamysqldatetimetimezonejodatime
Matt
Matt

Reputation: 5778

MySql Applications in Different Time Zones Give Different Results

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?

Upvotes: 1

Views: 395

Answers (2)

Barmar
Barmar

Reputation: 781004

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'

Upvotes: 2

B. van den Hoek
B. van den Hoek

Reputation: 74

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 ) );

Upvotes: 0

Related Questions