Reporting: you’re doing it wrong

I’ve often said that there are certain things average application programmers just do not get. And those are:

  • Calendars and timezones
  • Character encodings, Unicode, etc.
  • L10n & I18n in general
  • Relational databases
  • Networking (as in OSI)
  • Operating systems

And by “do not get” I do not mean “are not experts in”. I mean, they don’t know what they don’t know. Time and time again I see evidence of this. Recently I saw one that was so bad it was good — and that, I think, necessitates a meta-like amendment to this list, in the spirit of Dunning-Kruger. As in:

You are probably not the first person to have this problem. It is very likely that smarter (yes, snowflake) people already solved this problem in some tool or library that has endured for years, if not decades. USE IT!

Here is the incident, worthy of The Daily WTF.

There is a monthly report that business runs (it doesn’t really matter what kind of report — some numbers and dollars and stuff). How is the report being generated? (Leaving aside for now the question of why one would roll one own‘s reports, rather than using a ready-made BI/reporting solution. Using the brilliant algorithm that Donald Knuth forever regrets not including in his TAO series:

  1. Set current_time to the chosen start date, at midnight.
  2. Output the relevant data from day specified by current_time.
  3. If current_time is greater than the chosen end date, exit.
  4. Increment current_time by 86400 (because 86400 is a universal constant).

What could possibly go wrong?

Nothing. Except when you hit the “fall back” time (end of DST). Once you go past that date, the system will subtract an hour, and you end up at 11pm the previous day, not midnight of the day you wanted. And because you’re stupid, you have no idea why all your business users are waiting for those reports forever.