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.
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.
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:
If you are using Java directly, you should use the following connection string:
Of course, you should adjust the server timezone value accordingly. In our case, America/New_York was the best solution.