I'm querying a SQL database with DatabaseLink and resulting SQLDateTime objects are shifted by an hour during daylight saving time. The dates are stored in the database without a DST shift, so it seems like Mathematica is applying the change. How can I eliminate the spurious shift? A workaround, such as getting AbsoluteTime-style integers instead of SQLDateTime expressions would be fine.

  • $\begingroup$ Could you check the setting of $TimeZone? Alternatively, is your OS currently set to DST? $\endgroup$
    – J. M.'s torpor
    Feb 7 '12 at 16:57
  • $\begingroup$ It is set to -7.. What does that tell us? $\endgroup$ Feb 7 '12 at 17:43
  • 1
    $\begingroup$ What kind on database (HSQL, MySQL, other) is this? $\endgroup$ Feb 7 '12 at 20:44
  • $\begingroup$ It's a MySQL database to which I connect using Connector/J. $\endgroup$ Feb 7 '12 at 22:08
  • $\begingroup$ I had suspected that maybe Mathematica was doing the shift based on the timezone setting in your OS, but I think WReach has given the actual reason. $\endgroup$
    – J. M.'s torpor
    Feb 7 '12 at 23:53

DatabaseLink uses Java Database Connectivity (JDBC) internally. The behaviour you describe is a known, long-standing, and annoying bug in JDBC. The problem is that Java inappropriately attempts to apply daylight savings time adjustments to dates in the database -- even though such adjustments are likely to have taken place already. This bug occurs even if the server and client machines are configured to be in the same time zone. There is no easy workaround to this problem as long as the class java.sql.Date (et al) is in the loop. Non-trivial workarounds involve things such as trying to configure the entire database stack to use UTC. Also, some JDBC drivers provide alternate data-type mapping for date/time types.

However, the simplest workaround is to change your SQL statements to return dates as strings. This side-steps all of the JDBC date arithmetic nightmare. In most dialects of SQL, this means changing:

SELECT ... myDate ...

to something like


Unfortunately, the exact syntax is not standardized so you will have to look it up for your dialect of SQL.

In SQL Server, for example, we have a couple of options:


returns a localized string like "Feb 7 2012 8:56PM". Mathematica has no trouble interpreting this:

"Feb  7 2012  8:56PM" // AbsoluteTime // DateString
(* Tue 7 Feb 2012 20:56:00 *)

However, we might want to take out the vagaries of localization by converting dates to a fixed format instead. Again, using SQL Server as an example:


returns an ISO-like date string: "2012-02-07 20:56:32.733". Again, you will have to adjust the syntax to suit your specific dialect of SQL.

  • $\begingroup$ This is exactly the kind of solution I was looking for. In my database's SQL dialect, SELECT CAST(myDate AS CHAR) was the right way to express it. Thank you. $\endgroup$ Feb 8 '12 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.