Monday, October 21, 2019 Eric Richards

Dates and Times are terrible Part 437

As anyone who has done any professional programming knows, dates and times are a gawdawful field of landmines, full of things that seem like they should be easy but end up giving you grey hairs and taking years off your life. If you haven't already, take ten minutes and watch this Computerphile clip on YouTube:

If you haven't driven forks into your eyes and decided to go off and live in a cabin in the woods off-the-grid yet, here's yet another fun thing I learned today about dealing with dates, and specifically, the .NET DateTime class.

So, like all enterprise products, one of the things I work on has a reporting section, where you can specify some filters and run some reports, and we slice and dice the data an serve it up in some pretty charts. It is pretty basic stuff, and mostly I wrote it years and years ago, and haven't had many problems with it. Today, however, a nasty little bug surfaced that taught me some fun things about DateTime's and the .AddMonths() method that it exposes.

Our reporting interface has a couple of date picker controls that users use to specify the date range that they want to run a report over. Pick a start date, pick an end date, click a button, and a request trundles off to the server, which churns through some data, collates it, and returns a bar graph representing counts for various slices of time. We do some very basic automatic scaling on this, so that if you pick a single day, we break the data down by fifteen minute or hourly intervals, or daily intervals for longer ranges, or monthly, for the longest time ranges.

We have a function, let's call it GetMonthlyIntervals(), that takes a start and an end date, and slices that time span up into a set of start/end intervals that are later used to segment the data for the report. The naive version that I wrote many years ago and we have been using since without much change looked something like this:

public List<Tuple<DateTime, DateTime>> GetMonthlyInvervals(DateTime start, DateTime end){
	var ret = new List<Tuple<DateTime, DateTime>>();
	
	var temp = start;
	while (temp <= end){
		ret.Add(new Tuple<DateTime, DateTime>(temp, temp.AddMonths(1)));
		temp = temp.AddMonths(1);
	}
	return ret;
	
}

It's pretty simple, and most of the time it works just fine. But it is terribly, terribly wrong... However, for years it sailed along with it's awfulness unexposed.

As always with reporting, users prefer to see everything translated into their own local timezone. However, local times are hell to deal with on the server side, so you tend to convert everything and store it in a canonical timezone, like UTC. What that means is that you have to take the date/time values that the user provides, convert them to UTC, run your calculations, and then convert them back to UTC when you display the results.

So, being as I am on the East Coast of the USA, when I select a start time for a report like January 21, 2019, that's going to be 2019-1-21T00:00.0000000-04:00 in ISO-8601 format. If I convert that to UTC, it changes to 2019-1-21T04:00:00.0000000Z. Using the GetMonthlyInvervals() method above, if my end date is March 21, 2019, I'll get these intervals back, in UTC:

  • [ 2019-1-21T04:00:00.0000000Z, 2019-2-21T04:00:00.0000000Z ]
  • [ 2019-2-21T04:00:00.0000000Z, 2019-3-21T04:00:00.0000000Z ]
  • [ 2019-3-21T04:00:00.0000000Z, 2019-4-21T04:00:00.0000000Z ]

So far so good.

Now, let's say that I'm trying to generate a report from January 1st to April 1st, except I'm in India, so my timezone is UTC+530:

  • [2018-12-31T18:30:00.0000000, 2019-01-31T18:30:00.0000000]
  • [2019-01-31T18:30:00.0000000, 2019-02-28T18:30:00.0000000]
  • [2019-02-28T18:30:00.0000000, 2019-03-28T18:30:00.0000000]
  • [ 2019-03-28T18:30:00.0000000, 2019-04-28T18:30:00.0000000]
Here, we have a problem, and it's actually a pretty interesting one.
  1. We add a month to December 31st, and we get January 31st, as expected.
  2. Then we add a month to January 31st. What happens here? Naively, you might expect this to just go to February 31st, but the .NET DateTime is smart enough to cap the date to the end of February, so you get the 28th.
  3. Now we are adding a month to February 28th, and that gets us March 28th. Whoops, this is not what we wanted...

This drift results from incrementally adding