MySQL 6.0.5 and timezone offset


#1

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'}

#2

Hi @freddybob,

I’m tested with your above code. Issue happened on MySQL 6.0.5 when insert a column in timestamp or datetime type into DB table. It working as expected on MySQL 5.1.38. If I’m used a string for the timestamp or used cfqueryparam for that column means it works fine. So please rise ticket for this issue on Jira (https://luceeserver.atlassian.net/).


#3

Thanks @cfmitrah, I have raised a ticket.