Best way to get record counts grouped by month, adjusted for time zone, using SQL or LINQ to SQL
- by Jeff Putz
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.
