Stealth daylight saving shift in SQL data

Question

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.

Answer 1

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.