difference in datetime stored in mysql 5.7 and mysql 8 by grails?

Question

I ll try to make the issue i am facing as clear as possible.

i have a Registration domain with datecreated field.

I have an old grails 2.2 app that uses mysql 5.7. The application is run with timezone US/Mountain.

When i create a registration in grails 2.2 with mysql 5.7 then the date is stored as US/Mountain time as shown below.

I have upgraded the grails 2.2 app to grails 4.0.10. The new grails 4.0.10 app uses mysql 8. The new app runs fine but it stores the date in UTC timezone even though i pass the timezone in build.gradle file as

bootRun {
    ignoreExitValue true
    jvmArgs(
        '-Dspring.output.ansi.enabled=always',
            '-Duser.timezone=US/Mountain',
            '-Dgrails.server.port.https=8443',
            '-Dgrails.server.port.http=8080',
//            '-Dorg.apache.catalina.session.StandardSession.ACTIVITY_CHECK=true',

            '-noverify',
        '-XX:TieredStopAtLevel=1',
        '-Xmx1024m')
    sourceResources sourceSets.main
    String springProfilesActive = 'spring.profiles.active'
    systemProperty springProfilesActive, System.getProperty(springProfilesActive)
}

but it reads fine as US/Mountain.

here is the datetime stored by grails 4.0.10 in utc timezone.

Now the issue i am facing is. We have production data stored in mysql 5.7 where dates are stored in US/Mountain.

I am transferring the data to mysql 8 for the new app. I downloaded the data from mysql 5.7 as .sql file. I then imported the .sql file to mysql 8 running in my localsystem. The import works fine. When i run the new grails 4.0.10 app then i can see the datetime are all off by 7 hours. This is expected because grails 4.0.10 expects date in the database in UTC format but mysql 5.7 data is in US/Mountain.

So i am wondering how to convert this? The only way i know to fix this issue is to manually convert all dates in mysql 5.7 to UTC and then import the file to mysql 8 so that grails 4.0.10 can work correctly since it expects the datetime in UTC.

I appreciate any insights into this dilemma i have been facing for a few days.

Thank you very much in advance!

Answer 1

this one line in the application.yml did it.

hibernate:
    jdbc:
        time_zone: US/Mountain

Since i am importing mysql 5.7 data, which stored datetime in US/Mountain, to mysql 8 i need to tell grails 4 to interpret the datetime as US/Mountain rather than the default which seems to be UTC.

thank you olavgg from grails slack community. grails.slack.com for helping me.