Has anyone else encountered the JDBC Daylight Savings Time bug?

I have confirmation from MathWorks support of the following bug in the JDBC driver used by (at least) the Windows 64-bit R2012b Database Toolbox: datetimes falling within the hours lost (gained) due to the change to (from, respectively) Daylight Savings Time are quietly and unilaterally adjusted for DST, even if the target data-type is supposed to be time zone and DST "agnostic," e.g., SQL Server's datetime2 data-type. For example, if you query a SQL Server datetime2 field whose value is '2011-03-13 02:00:00' (which doesn't exist in (at least) US DST-observing locales, but is a valid value in UTC, as well as in places that don't observe the US DST change-over), Matlab will return '2011-03-13 03:00:00' (I don't know if this is location/configuration dependent: if your computer's time configuration has "adjust for DST" turned off, perhaps you won't see this behavior). The advertised workaround is to query for the value cast as a varchar (or equivalent), and then convert to a datetime-type in Matlab.

What I would like to know: has anyone else here observed this bug yet?

Thanks for your time; hope this helps someone(s) have a little less frustration than I've had. ;-)

OlyDLG

[Jamie Taylor]

I too am experiencing this problem. When I query my MySQL database in Matlab using the JDBC connector, datetime fields (no timezone info a.k.a "time-zone naive") where the datetime falls on the DST transition for my local timezone (GMT) are converted to the DST time (BST in my case).

e.g. The datetime '2014-03-30 01:00:00' is coming out in Matlab as '2014-03-30 02:00:00'

This is highly undesirable as it leads to duplicates in my hourly time series data which is in UTC and therefore does NOT need correcting for DST.

Did you ever find a solution to stop this that doesn't involve casting to varchar and then converting in Matlab? This results in performance hit :(

I'm using 2014b with mysql-connector-java-5.1.28

[Julia]

Has anyone determined the solution to this issue? I'm experiencing the same problem on R2016b.

[Andrew Stamps]

I just encountered the same thing on R2016b this past weekend. Any other recommendations on this?

[Ameya Deoras]

I am struggling with this issue as well. I have created a function fixDSTDupTable to post-process the data and try to correct the duplicate time-stamps. The function is attached. The other function is another utility function that is required by the first. Take a look at the code before using to verify that it does what you need.

Answer 1

[Martijn]

This conversion actually happens inside the JDBC driver and what it probably does is assume that the date/time which you specified was specified in the default Java TimeZone as is used inside Java inside MATLAB. This default Java TimeZone is based on your system settings.

So, one solution to this issue could be to change the default Java TimeZone inside MATLAB, for example set it to UTC which does not have DST using the following command:

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone('UTC'));

Answer 2

[Janice Goodenough]

Yes, I am encoutering the same or a related issue (R 2018b using JDBC driver and data are being automatically 'converted' to CET when using sqlwrite).

I have created a Mathworks support ticket for this (case number 03379450)

Given that this bug is quite critical and has obviously existed since 2013 I am very surprised they do not seem to care very much about fixing it..

[Janice Goodenough]

Other places have suggestes that it is an issue within JDBC connector, and that this can be fixed by appending the following settings (or a combination thereof) to your connection URL:

useLegacyDatetimeCode=false&serverTimezone=UTC&sessionVariables=time_zone=''+00:00''

e.g.

url = 'jdbc:sqlserver://10.100.105.00;database=DB01;useLegacyDatetimeCode=false&serverTimezone=UTC';

However, this did not fix the problem for us - More info about this approach can be found here:

https://vladmihalcea.com/how-to-store-date-time-and-timestamps-in-utc-time-zone-with-jdbc-and-hibernate/

Answer 3

[Janice Goodenough]

Hi again,

I got the issue resolved using Mathworks support - thank you to Martijn Aben for the quick help!

Posting the solution here in case somebody else has the same issue:

This conversion actually happens inside the JDBC driver and what it probably does is assume that the date/time which you specified was specified in the default Java TimeZone as is used inside Java inside MATLAB. This default Java TimeZone is based on your system settings.

So, one solution to this issue could be to change the default Java TimeZone inside MATLAB, set it to UTC using the following command:

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone('UTC'));