Copy link to clipboard
Copied
After upgrading MySQL to 8 we noticed some odd date behaviors.
Example:
2024-06-28T02:11:51.000 stored in a varchar type field named tstamp
When we execute this statement in mysql workbench directly:
STR_TO_DATE(tstamp,'%Y-%m-%dT%H:%i') as listedon
The result is correct: 2024-06-28 02:11:00
The same exact query when done using a coldfusion query on the same field and database returns:
2024-06-27 22:11:00.0
The coldfusion SELECT is subtracting 4 hours from the date stored in a varchar field when we use str_to_date to format it?
Needless to say this is causing issues. Does anyone know of a possible cause or fix for this maybe changing db drivers so that I don't have to recoded all queries on our sites using str_to_date?
Thanks
Forrest
Likely to be caused by the difference in time-zone netween MySQL and ColdFusion. If so, then you can solve the problem by changing the setting for MySQL time-zone, ColdFusion time-zone or both.
I shall use my settings to illustrate.
Steps to change the MySQL time-zone setting:
Copy link to clipboard
Copied
Likely to be caused by the difference in time-zone netween MySQL and ColdFusion. If so, then you can solve the problem by changing the setting for MySQL time-zone, ColdFusion time-zone or both.
I shall use my settings to illustrate.
Steps to change the MySQL time-zone setting:
Steps to change the ColdFusion time-zone setting:
.
Copy link to clipboard
Copied
I ran the following test on ColdFusion 2023 Update 8, using the MySQL 8.3.0 Jar:
<cfscript>
tstamp="2024-06-28T02:11:51.000";
dt=queryExecute("select STR_TO_DATE('#tstamp#','%Y-%m-%dT%H:%i') as listedon from testTable limit 1", [], {datasource="myTestDS"});
writeoutput(dt["listedon"][1]);
</cfscript>
The result was: 2024-06-28 02:11:00
Copy link to clipboard
Copied
Thanks! I set the serverTimezone in my connection strings to the one CF 2021 was using and it worked.
Strange that with previous version of MySQL this was not required so that MySQL server must have been set to use our time zone instead of the UTC default.