Count entries in postgresql grouped by date

rubyonrails-talk
1

Hi!

I have a table with visits with a visited_at: attribute which is a datetime field. They seem to be stored as UTC. Now I want to count all visits each day and return something like:

{

2013-01-01: 8,

2013-01-02: 4,

2013-01-07: 9,

…

}

So, I did it like this which kind of works…:

def self.total_grouped_by_day(start_date, end_date)

visits = where(visited_at: start_date…end_date)

visits = visits.group(“date(visited_at)”)

visits = visits.select(“date(visited_at) as date, count(visits.id) as total_visits”)

visits = visits.order(“date ASC”)

visits.group_by { |v| v.date.to_date }

end

It doesn’t return exactly the format I want but that’s not the big problem. The problem is that if a visit happens near midnight it may be counted at the “wrong” date due to time zones. I understand why, because “date(visited_at)” doesn’t know anything about my timezone.

Any good ideas on how to fix this issue?

2

I’ll just don’t do the group and count in the database for now. I do it like this instead:

def self.total_grouped_by_day(start_date, end_date)

visits = where(visited_at: start_date…end_date)

visits = visits.order(“visited_at ASC”)

visits.group_by { |v| v.visited_at.to_date }

end

And then just use visits[date].count for the counting. There will probably not be that many records anyway…

3

You're looking for the "AT TIME ZONE" construct.