Stealth daylight saving shift in SQL data

8

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.

ArgentoSapiens

Posted 2012-02-07T16:39:57.387

Reputation: 7 510

Could you check the setting of $TimeZone? Alternatively, is your OS currently set to DST? – J. M.'s ennui – 2012-02-07T16:57:44.130

It is set to -7.. What does that tell us? – ArgentoSapiens – 2012-02-07T17:43:10.710

1What kind on database (HSQL, MySQL, other) is this? – Arnoud Buzing – 2012-02-07T20:44:20.737

It's a MySQL database to which I connect using Connector/J. – ArgentoSapiens – 2012-02-07T22:08:18.247

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. – J. M.'s ennui – 2012-02-07T23:53:55.890

Answers

7

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

SELECT ... CAST(myDate AS VARCHAR) ...

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:

SELECT CAST(GETDATE() AS VARCHAR)

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:

SELECT CONVERT(VARCHAR, GETDATE(), 121)

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.

WReach

Posted 2012-02-07T16:39:57.387

Reputation: 62 787

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. – ArgentoSapiens – 2012-02-08T15:47:03.913