I’ve spent too many working days figuring out how to handle dates and times in relational data base management systems. From looking at the questions on stackoverflow.com it’s obvious that a lot of people have the same kinds of questions and confusions I have.
I’m a lazy programmer. I’d much rather do this stuff right than do it over, and I don’t think I’m alone.
A big challenge of this date and time work is the lack of useful standardization for the working programmer. Oracle handles dates and times one way, MySQL handles them a different way, PostgreSQL another way, Microsoft applications another way, Unix and Linux yet another way, and so forth. (My British schoolteacher would have said, etcetera, etcetera, ad nauseam.) It has ever been so: the ancient Hebrews and Babylonians had their own calendars just like various software systems do now. Then, on top of that, we have time zones, the international date line, and leap seconds.
The conflicting ways of denoting the passage of time offer the software developer a stark choice.
- Swear fealty to one of the systems for marking dates and time, and forget about the others
- Go all postmodern and multicultural, and try to make software that will work with more than one system
I prefer the postmodern approach. But, either approach is hard to do right. The first step to doing any of this the lazy way is to get the concepts right.
There’s a fine book on this topic by Richard T. Snodgrass called Developing Time-Oriented Database Applications in SQL (San Francisco: Morgan Kaufman, 1999). It’s out of print, but Prof. Snodgrass has generously made it available online.