UTC - Working one day behind

Question

I have an application which records statistics. When a user submits their statistics the information is stored and the date/time is recorded as UTC e.g.

In the UK if I recorded figures at 03/08/2010 10:30 my recorded date/time would be 03/08/2010 09:30 as we are currently observing DST.

If I was in say Australia, and I recorded my statistics at the same time locally my recorded date/time would be 02/08/2010 23:30 as I think they are around 10 hours ahead.

My application needs to generate a statistical report at 7am every morning (local time) for everyone. So when it runs it does something like this:

var currentUtc = DateTime.UtcNow.AddDays(-1); // UTC date/time 24 hours from current

This date (time is ignored) is then used to pull the statistics from the database. Now this is working fine for the UK. It is other international countries I am finding the problems. More specifically Australia.

The issue I have is Australia are 10 hours ahead so my application is being triggered to run at around 03/08/2010 22:00 UK time to generate a report for 04/08/2010 07:00 Australia time. So what is happening is something like:

// the date/time at this point would be 03/08/2010 22:00 (UTC 03/08/2010 21:00)
var currentUtc = DateTime.UtcNow.AddDays(-1);
// so currentUtc is set to 02/08/2010 22:00 (which makes sense for UK, but not for Aussie)

If the above was a report for someone in the UK it means they would receive a report for 02/08/2010 on 03/08/2010 which is correct. However, for Australia (as they are 10 hours ahead) that means they would be receiving a report for 02/08/2010 on 04/08/2010 which would be 2 days behind.

What should be happening is when my application runs at 22:00 hours on 03/08/2010 it should be generating a report for Australia which is for 03/08/2010. However, as per the setup above you can see why this isn't happening.

I can't quite seem to put my finger on this one? I have a feeling it is something to do with my ignoring the time and just relying on the UTC date but I am not sure.

Answer 1

Because the user's day may not start and end at midnight UTC time, your date field in the database needs to include the time as well. Otherwise your Australian report will contain more than 24 hours of statistics. The date range from 02/08/2010 to 04/08/2010 only looks odd if you ignore the time. If you include the time and double-check the range, you'll see that it does contain 24 hours just like intended. When you convert it back to local time for displaying the report it will be fine.

Answer 2

the mismatch between "query at 7am local time" and "previous day UTC" seems to be odd IMHO - I'd imagine the user would instead want the previous day of data, with 'day' being in their local time, so the range you would want to search within is DateTime.Today.AddDays(-1) to DateTime.Today (so it covers 'yesterday, local time'), and since you're storing in UTC, we just add in ToUniversalTime to each of those to get them into UTC for querying the database.

var startOfToday = DateTime.Today.ToUniversalTime();
var startOfYesterday = startOfToday.AddDays(-1);

var query = ...