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?
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/-quw1tgZyhQJ.
For more options, visit https://groups.google.com/groups/opt_out.
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:>
> 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?
>
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/dfMrP9b0wc8J.
For more options, visit https://groups.google.com/groups/opt_out.
You''re looking for the "AT TIME ZONE" construct. On Jan 29, 2013, at 9:26 AM, Linus Pettersson wrote:> 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? > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/-quw1tgZyhQJ. > For more options, visit https://groups.google.com/groups/opt_out. > >-- Scott Ribe scott_ribe-ZCQMRMivIIdUL8GK/JU1Wg@public.gmane.org http://www.elevated-dev.com/ (303) 722-0567 voice -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit https://groups.google.com/groups/opt_out.