Couple of years ago I wrote a small application to manage a post cards distribution for a small advertising company. The application worked with a Cloudscape database but it’s not the point. The point is that my friend, who was working for that company, asked me to help with some strange problem. After the hardware upgrade all of the records in the database became invisible. I need to say that there is a screen where you can examine the amounts of cards sent to a different organizations at the given dates, and there is another screen where you can build a comprehensive reports with the same information but for a given dates range. Well, the records were not visible on the single-date statistics page, but they were in the reports. I was shocked…

Here come some implementation details. In the application I decided to store all dates as “long” numbers (the standard Java timestamp format). The customers didn’t need hours, minutes and lower resolution values so that I was clearing them. As consequence everything in the application happened exactly in the midnights and it was OK because no one cared about time values.

Timestamps, harware upgrades, dates, time-zones… Yes, it was all about the time. The records, entered when application was running on the previous server had time recorded in one time-zone and when they were fetched on the new server, which is in fact in the different time-zone, they had the time values shifted by the difference between these two time-zones. So that “2003-12-24 00:00:00″ was becoming “2003-12-23 23:00:00″. Of course, the direct query to “2003-12-24 00:00:00″ was giving nothing. Weird! But I do certainly understand the issue… now.

There were two possible solutions I could see:

  • Align the time-zone setting of the second server to the first one
  • Switch to using 20050130 type of numbers to represent dates.

Yeah, the first one looks fast, but is rather unprofessional. And the second requires significant rework and database cleanups. The choice always depends.

Beware of using timestamps to record dates in a database when a time-zones shift is possible due to the move or when there are several database clients located in different time-zones! It won’t work out!