Cannot Reproduce
Details
Details
Assignee
Michael Offner
Michael OffnerReporter
Fred B
Fred BPriority
CriticalLabels
New Issue warning screen
Before you create a new Issue, please post to the mailing list first https://dev.lucee.org
Once the issue has been verified, one of the Lucee team will ask you to file an issue
Sprint
None
Created 24 January 2018 at 12:19
Updated 21 June 2021 at 21:34
Resolved 20 February 2019 at 07:22
Using MySQL 6.0.5, when I insert timestamps into my MariaDB database table, dates which fall within daylight savings are being offset by an hour.
This does not happen using MySQL 5.1.38.
The datasource is defined to use the same timezone as my Lucee instance (Europe/London). It makes no difference if the Legacy Datetime Code option is enabled or not. It happens whether the database column is a datetime or a timestamp.
Using a string for the timestamp or using cfqueryparam solves the problem.
<!--- in every example, myTimestamp should be set to 12pm on August 1 2018 ---> <!--- update timestamp using createDateTime() ---> <cfquery name="updateRecord" datasource="myDSN"> UPDATE myTable SET myTimestamp = #createDateTime(2018, 08, 01, 12, 00, 00)# WHERE unique_id = 1 </cfquery> <cfquery name="selectRecord" datasource="myDSN"> SELECT * FROM myTable WHERE unique_id = 1 </cfquery> <cfdump var="#selectRecord.myTimestamp#" format="simple" /> <!--- update timestamp using createOdbcDateTime() ---> <cfquery name="updateRecord" datasource="myDSN"> UPDATE myTable SET myTimestamp = #createOdbcDateTime(createDateTime(2018, 08, 01, 12, 00, 00))# WHERE unique_id = 1 </cfquery> <cfquery name="selectRecord" datasource="myDSN"> SELECT * FROM myTable WHERE unique_id = 1 </cfquery> <cfdump var="#selectRecord.myTimestamp#" format="simple" /> <!--- update timestamp using a string ---> <cfquery name="updateRecord" datasource="myDSN"> UPDATE myTable SET myTimestamp = '2018-08-01 12:00:00' WHERE unique_id = 1 </cfquery> <cfquery name="selectRecord" datasource="myDSN"> SELECT * FROM myTable WHERE unique_id = 1 </cfquery> <cfdump var="#selectRecord.myTimestamp#" format="simple" /> <!--- update timestamp using cfqueryparam ---> <cfquery name="updateRecord" datasource="myDSN"> UPDATE myTable SET myTimestamp = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#createDateTime(2018, 08, 01, 12, 00, 00)#" /> WHERE unique_id = 1 </cfquery> <cfquery name="selectRecord" datasource="myDSN"> SELECT * FROM myTable WHERE unique_id = 1 </cfquery> <cfdump var="#selectRecord.myTimestamp#" format="simple" />expected result:
Date Time (Europe/London) {ts '2018-08-01 12:00:00'} Date Time (Europe/London) {ts '2018-08-01 12:00:00'} Date Time (Europe/London) {ts '2018-08-01 12:00:00'} Date Time (Europe/London) {ts '2018-08-01 12:00:00'}actual result:
Date Time (Europe/London) {ts '2018-08-01 11:00:00'} Date Time (Europe/London) {ts '2018-08-01 11:00:00'} Date Time (Europe/London) {ts '2018-08-01 12:00:00'} Date Time (Europe/London) {ts '2018-08-01 12:00:00'}