Has anyone else encountered the JDBC Daylight Savings Time bug?

15 次查看（过去 30 天）
David Goldsmith
David Goldsmith 2013-3-1
回答： Janice Goodenough 2018-12-12
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 个评论
显示 2更早的评论
Andrew Stamps
Andrew Stamps 2018-3-13
I just encountered the same thing on R2016b this past weekend. Any other recommendations on this?
Ameya Deoras
Ameya Deoras 2018-3-14
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（3 个）

Martijn
Martijn 2018-12-12
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 2018-12-12
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 个评论
Janice Goodenough
Janice Goodenough 2018-12-12
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/

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by