Careful with those dates!

Crap! I've been bit in the ass by dates AGAIN.

Back at Microsystems/Biotronik, I got screwed assuming the SQL Server could tell the difference between dates that were milliseconds apart. Turns out, they have to be several milliseconds apart, or they look like the same date. When you're tracking items as they moved through a manufacturing line and machines are pushing the parts around, milliseconds are critically important. If you want to use date as part of a compound primary key or unique index, you have to use a epoch-based value (or something other than SQL Server).

And once again, dates are back to haunt me! This time, we're using dates to organize files on the filesystem. Awesome has a concept of "Assets", which is simply binary content (pictures, spreadsheets, documents, etc.). For each binary file, we keep a record (in the database) of metadata. The actual file is stored on the file system. We decided that in order to potentially partition and sort the files, we would store them based on their created date. In other words, the "date added" value in the metadata record tells us where the binary file is stored. This works great within the overall Awesome architecture.

That is, until yesterday!

Turns out, the database was misconfigured. While we're on the West coast, the database thought it was somewhere in the midwest. One of our diligent IT staff noticed this incosistency, and like any good IT person would, he corrected the problem. Hours later, we were troubleshooting a problem with Assets not being found. Now, the metadata that used to point to 2009/06/12/10/10/24 as a path for the Asset was looking in 2009/06/12/12/10/24. Opps, no file!

BTW, if you're wondering why we use the date and don't store the actual path as a varchar, consider what happens when we want to change how the binary files are stored. While it would be painful to move each file to a new directory structure, changing the code to determine the path is much easier (and safer) to change than trying to update each record in the database. Just sayin' . . . :-)

Anyway, the database's timezone is back to central time and all is good. For the time being, we're going to write a script that moves all of the files to a different "hour" directory and then change the timezone. Fortunately, we don't have very many Assets that were uploaded between midnight and 2 am. Since the added time is only critical for finding the binary file represented by the record, updating a few records before we run the script should be pretty painless.

So, crisis averted. But next time, I'll pay more attention when choosing whether or not to use a date as an identifier!