Forum: Ruby on Rails Count entries in postgresql grouped by date

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?

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...

Den tisdagen den 29:e januari 2013 kl. 17:26:00 UTC+1 skrev Linus
Pettersson:

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

On Jan 29, 2013, at 9:26 AM, Linus Pettersson wrote:

> So, I did it like this which kind of works...:
> Any good ideas on how to fix this issue?
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby 
on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email 
to rubyonrails-talk+unsubscribe@googlegroups.com.
> To post to this group, send email to rubyonrails-talk@googlegroups.com.
> To view this discussion on the web visit 
https://groups.google.com/d/msg/rubyonrails-talk/-....
> For more options, visit https://groups.google.com/groups/opt_out.
>
>


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice