MySQL date and time shift on a query

We noticed a strange issue when working with MySQL in ColdFusion. The date and time in the database field were set to ‘2020-01-10 00:38’ but when it reached our web server, it was set to ‘2020-01-09 19:38’. There was a five hours difference between the database server and web server and we were not sure where from it came.

Investigation

During the investigation, we found that the database server is working using the UTC time zone. On the webserver, however, the US-East time zone was set. This was the first piece of the puzzle we had to solve.

We were not able to adjust the timezone on the database server nor on the webserver. We had to find the other way around.

The ColdFusion engine is built upon the Java language and it uses Java JDBC to connect to the database. We also learned that the MySQL connection is able to set a separate timezone for the particular connection.

The solution

Once we knew that the issue lays in the connection between MySQL and ColdFusion, we were almost sure what to do. In the ColdFusion administrator, you can set the connection string parameters. In our case we had to add this to the connection string:

useLegacyDatetimeCode=false&serverTimezone=America/New_York

If you are using Java directly, you should use the following connection string:

jdbc:mysql://[db-host-address]/[db-name]?useLegacyDatetimeCode=false&serverTimezone=America/New_York

Of course, you should adjust the server timezone value accordingly. In our case, America/New_York was the best solution.