Has anyone else encountered the JDBC Daylight Savings Time bug?

20 views (last 30 days)
David Goldsmith
David Goldsmith on 1 Mar 2013
Answered: Janice Goodenough on 12 Dec 2018
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
  4 Comments
Ameya Deoras
Ameya Deoras on 14 Mar 2018
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.

Sign in to comment.

Answers (3)

Martijn
Martijn on 12 Dec 2018
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'));

Janice Goodenough
Janice Goodenough on 12 Dec 2018
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..
  1 Comment
Janice Goodenough
Janice Goodenough on 12 Dec 2018
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/

Sign in to comment.


Janice Goodenough
Janice Goodenough on 12 Dec 2018
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'));

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!