Best way to get record counts grouped by month, adjusted for time zone, using SQL or LINQ to SQL

Question

I'm looking for the most efficient way to suck out a series of monthly counts of records in my database, but adjusting for time zone, since the times are actually stored as UTC. I would like my result set to be a series of objects that include month, year and count.

I have LINQ to SQL objects that looks something like this:

public class MyRecord {
   public int ID { get; set; }
   public DateTime TimeStamp { get; set; }
   public string Data { get; set; }
}

I'm not opposed to using straight SQL, but LINQ to SQL would at least keep the code a lot more clean. The time zone adjustment is available as an integer (-5, for example). Again, the result set what I'm looking for is objects containing the month, year and count, all integers.

Any suggestions? I can think of several ways to do it straight, but not with a time zone adjustment.

EDIT: The answer below got me headed in the right direction. This is what I ultimately ended up with:

var counts = _context.MyRecord
    .Select(r => new {original = r.TimeStamp, adjusted = TimeAdjust.GetAdjustedTime(Config.TimeZoneAdjustment, r.TimeStamp)}).ToArray()
    .GroupBy(r => new {r.adjusted.Month, r.adjusted.Year})
    .Select(g => new MonthCount { Count = g.Count(), Year = g.Key.Year, Month = g.Key.Month })
    .OrderByDescending(g => g.Year).ThenByDescending(g => g.Month);

Basically I'm pulling all of the date down, which works OK given the limited scope of this app. The TimeAdjust function gets "real" adjusted times, accounting for DLS. The ToArray() call is made to avoid the lazy execution that pisses of SQL because of the time adjustment function.

Answer 1

context.MyRecords

    // tz adjusted, projection
   .Select(r => new {original = r.TimeStamp, adjusted = r.TimeStamp.AddHours(tz)})

   // group by start of month
   .GroupBy (r => r.adjusted.Date.AddDays(-r.Day))

   // final projection from groups to values asked for
   .Select (g => new {count = g.Count(), year = g.Key.Year, month = g.Key.Month})

Note: As @dana points out, if you want to account for daylight savings time too this is a whole lot more complex. Grouping by offset times ignoring DST will get a good enough answer for most reports because the only values that will be wrongly accounted for are on month boundaries, around midnight for a few months of the year.

IF you do want to account for DST it's more complex than just looking at the date range and adding an extra hour. Historically both the boundaries and the offsets of timezones have changed, so to do this right you need a historical record of these changes and you need to know the location of the user. A simple TZ offset does not get you this.

IF you account for DST you also need to make sure you don't lose an hour or double count an hour twice a year.

It's nigh on impossible to get this 100% right if you account for DST which is why the TZ offset only approach is the better bet.

Answer 2

One thing to point out is that timezone offsets can change throughout the year for a given timezone...

• Mountain Standard Time (MST): GMT-7
• Mountain Daylight Time (MDT): GMT-6

If you wanted a montly rollup report for the first 6 months of a year, you would first have to compute the endpoints for each month:

• 2010-01-01 00:00:00 (MST) -> 2010-01-01 07:00:00 (GMT)
• 2010-02-01 00:00:00 (MST) -> 2010-02-01 07:00:00 (GMT)
• 2010-03-01 00:00:00 (MST) -> 2010-03-01 07:00:00 (GMT)

[TimeZone Change]

• 2010-04-01 00:00:00 (MDT) -> 2010-04-01 06:00:00 (GMT)
• 2010-05-01 00:00:00 (MDT) -> 2010-05-01 06:00:00 (GMT)
• 2010-06-01 00:00:00 (MDT) -> 2010-06-01 06:00:00 (GMT)
• 2010-07-01 00:00:00 (MDT) -> 2010-07-01 06:00:00 (GMT)

I don't know how to run this in LINQ, but to get a count using straight SQL, something like this would work:

select count(*) as TheCount, AdjustedDate

from
    (select *,
    case
        when TheDate >= '2010-01-01 00:07:00' and TheDate < '2010-02-01 07:00:00' then cast('2010-01-01 00:00:00' as DateTime)
        when TheDate >= '2010-02-01 00:07:00' and TheDate < '2010-03-01 07:00:00' then cast('2010-02-01 00:00:00' as DateTime)
        when TheDate >= '2010-03-01 00:07:00' and TheDate < '2010-04-01 06:00:00' then cast('2010-03-01 00:00:00' as DateTime)
        when TheDate >= '2010-04-01 00:06:00' and TheDate < '2010-05-01 06:00:00' then cast('2010-04-01 00:00:00' as DateTime)
        when TheDate >= '2010-05-01 00:06:00' and TheDate < '2010-06-01 06:00:00' then cast('2010-05-01 00:00:00' as DateTime)
        when TheDate >= '2010-06-01 00:06:00' and TheDate < '2010-07-01 06:00:00' then cast('2010-06-01 00:00:00' as DateTime)
    end as AdjustedDate
    from YourTable
    where TheDate >= '2010-01-01 00:00:00' and TheDate < '2010-07-01 06:00:00') as nested

group by AdjustedDate

Its a lot of work, but would give you pretty accurate results. As far as getting the moving timezone offsets, that's a whole different problem :)

Answer 3

If you were using MySql, then you could use the function:

CONVERT_TZ(dt,from_tz,to_tz)

• converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 9.7, “MySQL Server Time Zone Support”