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
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.
See more on this question at Stackoverflow