I recently noticed that 2 tests were suddenly failing for no apparent reason. This was in a large codebase where we have a mix of two development approaches. The project is simply too big to convert everything at once to the latest development approach so we're using Ayende's Obsolete In Isolation approach on this. The 2 failing tests were in the old parts of the code, so i cringed and then set out to investigate.
The tests of the old part of the code aren't always easy to comprehend and they use the database all over the place. I figured i'd quickly run them on the database that we use when we run those tests on our local machines, and after that, run them on the database that the buildserver uses (which is rebuilt entirely with each build) to examine the differences between both test runs. They both passed in both cases. They were still failing on the buildserver though.
Upon first inspection of the code of those tests, it didn't seem like there was any blatantly obvious reason why those tests were failing on one machine, and not on the others. They were inserting some records with some manipulated DateTime values, and then calling some code which would execute a query which should have retrieved these values. The records inserted by the test were not retrieved for some reason, which caused the tests to fail.
DateTime values and tests can be tricky sometimes so i started looking in that direction. The queries that where executed in those tests would use the current date/time together with a margin of a couple of minutes to retrieve records where a certain date/time column's value would fall in the range of the current date/time with the given margin. The records that were inserted in the tests would use the current date/time, minus 2 minutes, or plus 2 minutes. Hmm... this could possibly be flaky depending on how the query determines the current date/time. If the query receives the current date/time from the calling code, then this wouldn't cause problems but if it uses the current date/time of the database, then this is not only a flaky test, but a real bug in the code.
I then checked the system clock of the buildserver, and as i suspected, it was about 4 minutes ahead of the system clock of the database server. Normally, all of our servers' system clocks are synchronized, but for some reason, the buildserver hadn't had its system clock synchronized since the day before. The code was inserting records in the database with modified date/time values, based on the system clock of the system the code was running on, and was then querying the database using the database's system clock. Since the time difference was big enough, the query would not return the expected rows and this made the tests fail on the buildserver.
Obviously, this never would've happened if the query would've used a parameter supplied by the caller which would've represented the current date. And that is the real bug that these flaky tests (which up until this particular incident never failed) highlighted. A situation like this could've occurred in production if the application server's clock and the database server's clock would've gotten out of synch. Can you imagine the pain you'd go through when investigating a bug report like this? :p
Date/Time values are often tricky, especially when there is more than one source which could determine the current Date/Time. So do yourself, and anyone who will maintain your code down the line, a favor by making sure that only ONE source is in charge of Date/Time values in your system. Using the database for all date/time values is definitely not always possible, so it's best to have your application server be in charge of all date/time values. If you need the current date/time in a query, simply supply it as a parameter to the query and you'll avoid problems like this.
Though this advice does leave me wondering what the best approach to deal with this is when you have multiple application servers which could assign date/time values.