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 did 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.
It does depends on the query syntax. 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="website">
UPDATE myTable
SET myTimestamp = #createDateTime(2018, 08, 01, 12, 00, 00)#
WHERE unique_id = 1
</cfquery>
<cfquery name="selectRecord" datasource="website">
SELECT * FROM myTable
WHERE unique_id = 1
</cfquery>
<cfdump var="#selectRecord.myTimestamp#" format="simple" />
<!--- update timestamp using createOdbcDateTime() --->
<cfquery name="updateRecord" datasource="website">
UPDATE myTable
SET myTimestamp = #createOdbcDateTime(createDateTime(2018, 08, 01, 12, 00, 00))#
WHERE unique_id = 1
</cfquery>
<cfquery name="selectRecord" datasource="website">
SELECT * FROM myTable
WHERE unique_id = 1
</cfquery>
<cfdump var="#selectRecord.myTimestamp#" format="simple" />
<!--- update timestamp using a string --->
<cfquery name="updateRecord" datasource="website">
UPDATE myTable
SET myTimestamp = '2018-08-01 12:00:00'
WHERE unique_id = 1
</cfquery>
<cfquery name="selectRecord" datasource="website">
SELECT * FROM myTable
WHERE unique_id = 1
</cfquery>
<cfdump var="#selectRecord.myTimestamp#" format="simple" />
<!--- update timestamp using cfqueryparam --->
<cfquery name="updateRecord" datasource="website">
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="website">
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'}