Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

MySQL 6.0.5 driver modifies timestamps with daylight savings

Description

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.

expected result:

actual result:

Environment

None

Attachments

1

Activity

Show:

Zac Spitzer 21 June 2021 at 21:34

I have disabled the first two failing tests on GHA, as explained they will not work

Fred B 6 December 2018 at 10:44

Yes, Michael's explanation makes sense, thanks.

Pothys - MitrahSoft 6 December 2018 at 06:03

Hi ,

Did you saw above comment?

Michael Offner 26 November 2018 at 14:47

JDBC driver that are used with cfquery do not support to pass along the timezone of the application server (sadly) with the query.
So if the database server receives a date without timezone information it will use the timezone of the database server as base.
If the application and database server are not using the same timezone you can get an offset like this.

So what is the difference from example 1 and 2 to 3.
1 and 2 are producing a date object "noon first of august 2018" of the timezone defined in the environment or if defined with the datasource itself


3) simply hardcoded noon to sql.

So this is not a bug as far i can see, this is simply a "limitation" of JDBC/CFML.
i assume that you have set "Europe/London" in Lucee, but GMT-0 for the database server.
what is the same for dates in winter, but differs for dates in summer.

How to solve this?
There are different ways to solve this

  • when passing dates use <cfqueryparam> or attribute "params" with a type definition.

  • make sure you have set the right timezone with the datasource definition

  • use the same timezone for the application and database

Testcase provided passes fine on travis that does not set any extra timezone.
Please provide further information if my assumption is incorrect.

Pothys - MitrahSoft 30 January 2018 at 09:31

I've added testcase for this ticket & Confirmed the issue happened on lucee while insert a column in timestamp or datetime field into DB table. If we using a string for that timestamp or using cfqueryparam in that column means it working fine, Otherwise it will displaying wrong time.

Pull Request : https://github.com/lucee/Lucee/pull/382

Cannot Reproduce

Details

Assignee

Reporter

Priority

Labels

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

Created 24 January 2018 at 12:19
Updated 21 June 2021 at 21:34
Resolved 20 February 2019 at 07:22