MySQL dates that work across multiple time-zones

Wouldn't it be nice if you could store date information in your database that would be correct regardless of your server and client time-zones (that may themselves change over time)?

One way to do this is to store all date information relative to the GMT time-zone regardless of the server and client time-zones. Whenever a client interacts with a date you simply convert it from GMT to the client's specific time-zone and back as needed.

Well, after 12 hours of investigation, I finally got this to work ;) Here's how:

1. Configure the server to use the GMT time-zone by inserting default-time-zone=UTC (case sensitive) in my.cnf
2. Set the useLegacyDatetimeCode MySQL driver parameter to false. This fixes this bug: http://bugs.mysql.com/bug.php?id=15604
   
3. When reading/writing dates from/to the database always use UTC dates

That's it.

Tracking Java Versions using Google Analytics (part 2)

Update: I just posted (December 4th, 2008) a version that will allow you to differentiate between 1.6.0* the family versus 1.6.0 the version.

I'm experimenting with a new script for tracking Java versions. To use the new code simply add the following two lines after your Google Analytics script:

<script src="http://java.com/js/deployJava.js" type="text/javascript"></script>
<script src="http://jre-analytics.googlecode.com/svn/trunk/jre-analytics.js" type="text/javascript"></script>

I encourage you to sign up to the mailing list to follow future discussions on this topic: http://code.google.com/p/jre-analytics/

Benefits of new script

1. Easier, auto-updating installation. Linking to my external script means that you get the benefit of future updates without any effort on your part.
   
2. Does not conflict with other scripts. The old code uses _setVar() which records at most one variable across an entire user session. Different scripts compete for exclusive access to this variable. The new code uses _trackEvent() which allows multiple values to be recorded in separate namespaces, so different scripts do not interfere with each other's data collection.
3. Records multiple JRE versions per user. The old script was supposed to do this but the limitations of _setVar() meant it only recorded the latest JRE version.

Details

About 48 hours after you install the script you should see new data under Content -> Event Tracking. Please note that the Event Tracking menu will not show up until you collect enough data.

Please let me know the statistics reported for your site. Post this at http://forums.java.net/jive/thread.jspa?messageID=317425

The new script will record two kinds of events:

1. detected: indicates whether Java was detected on the user's machine.
2. version: indicates the specific Java version detected on the user's machine.
   

This should give you a much better overview of what's really going on. I look forward to your feedback!

Warning

According to http://code.google.com/apis/analytics/docs/eventTrackerGuide.html both the old and new script will cause your page's "bounce rate" to incorrectly get reported as zero. This is a limitation of how _setVar() and _trackEvent() are implemented and there doesn't seem to be anything I can do about it. Sorry :(