Dynamic Linq Group By UnixTime

I am using Dynamic Linq. Group By works fine like this:

var groupedData = resultSet.AsQueryable().GroupBy("Username", "it").Select("new (it.Key as key, it.Count() as count)");

But I want to use unixtime column as key for grouping. Example table:

CreatedDate(integer)

1419258192 (22/12/2014 14:23:12)  
1419292800 (23/12/2014 00:00:00)  
1419349440 (23/12/2014 15:44:00)  
1419379200 (24/12/2014 00:00:00)  

What I want as a result:

22/12/2014: 1  
23/12/2014: 2  
24/12/2014: 1  
Jon Skeet
people
quotationmark

I would try to get out of the dynamic part as quickly as possible. You can then convert to DateTime and use the Date part for the grouping. If you need to be dynamic in the field name, you can do that, but end up with an IEnumerable<long> for further work:

var groupedData = resultSet.AsQueryable()
                           .Select("CreatedDate")
                           .AsEnumerable<long>()
                           .GroupBy(UnixTimestampToDate)
                           .Select(g => new { g.Key, Count = g.Count() });

...
private static readonly DateTime UnixEpoch =
    new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

private static DateTime UnixTimestampToDate(long timestamp)
{
    return UnixEpoch.AddSeconds(timestamp)
                    .Date;
}

If you need to convert to a different time zone, you could do that in UnixTimestampToDate... or make it part of the query itself by just converting from the unix timestamp to a UTC DateTime without calling Date, then transforming to a different time zone, then taking the date afterwards.

people

See more on this question at Stackoverflow