date ranges and half-open intervals

Half-open intervals:
http://hotmath.com/hotmath_help/topics/interval-notation.html
http://mathworld.wolfram.com/Half-ClosedInterval.html

Suggested way to store date ranges with TIMESTAMP fields:
http://stackoverflow.com/questions/156032/how-do-you-store-date-ranges-which-are-actually-timestamps

Above discussion particularly relevant to SQL Server 2005 which does not have native support for DATE, only TIMESTAMP (which they call DATETIME for historical reasons).

We need to record start and end dates of services.  Services dates are assumed inclusive, in other words the service will be available for the entire first day and last day as well as the days in between.  Our problem is SQL Server 2005 forces us to use a DATETIME (or SMALLDATETIME) type, so we have to specify a time component as well as a date.  For start date the start time is easy: 00:00:00, for end date the end time is more tricky.  We bill to the second so we could say 23:59:59 (this would be considered a closed interval) or we could say 23:59:59.999 (ie approaching the boundary) or even 00:00:00 the next day.  There are advantages and disadvantages to all options but in the end we went for the third option in the database, but display inclusive end date to the users as it's more intuitive (ie we followed deepc's advice).

Joe Celko's book Thinking in Sets, he seems to favour the second approach.  This thread mentions the different approaches (Kornelis seems to favour the third approach):
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/66078/Date-Range

If you get to use SQL Server 2008 - lucky you - make use of the new data types:
http://www.simple-talk.com/sql/t-sql-programming/time-gentlemen-please!-the-sql-server-temporal-datatypes/

MySQL SSL

We decided to change our architecture so the web server and database run on separate machines.  They are both behind a firewall, however for certain reasons we still decided the db connection should be SSL.

There are instructions on how to set up MySQL to support SSL connections here: http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html

It's fairly straightforward although following a server restart we found the following in our MySQL server logs:

SSL error: Unable to get private key

It seems the files generated are not in the correct format.  Somebody else experienced a similar problem: http://forums.mysql.com/read.php?11,400856,401127#msg-401127

We followed that advice, and the server now appeared happy, but the client still could not establish a SSL connection.  We noticed this wasn't a problem on our 64-bit platform and suspicion fell on the OpenSSL implementation.  Checking back at Shining Light, a comment attached to v1.0.0d says "Recommended for brave users" and describes v1.0.0d as a beta version.  Downgraded to v0.9.8r and everything works fine.

Raises an interesting point - the positioning of v1.0.0d on the download page and the slightly unorthodox version numbering tripped me up - I'm sure I'm not the only one.

Windows 2003: scheduled tasks and shortcuts

This tripped me up today.  Spent ages trying to figure why a scheduled task kept failing to run (Could not start), yet it ran just fine on the command line in the same user account.

Looking in the log (Scheduled Tasks window click View Log on the Advanced menu) I see:

Unable to start task.
The specific error is:
0x80070005: Access is denied.

[Note the log cycles, overwriting from the start when the log is full, so the latest entry is not necessary the last entry in the file]  The entry in the log is not very helpful.  Much wasted time later I realise scheduled tasks cannot handle shortcuts.