I am trying use activerecord to sum up calorie values of items posted on the same day in my timezone aware app. I''m running into problems with the time zone support. All my dates are stored in the DB in GMT and converted to local time which means for someone using EST time, these two items fall under the same date though their stored on different days in the DB since they''re stored in GMT. Here''s an example of the problem: Record 1: Post date: 2007-12-23 20:15:00 Calories: 130 Record 2: Post date: 2007-12-24 01:15:00 Calories: 100 If these post dates were converted to Eastern Standard time, they both have been posted on the same day, 12/23/07, which is how they''re being displayed on the site for someone with EST time. How do i retreive these entries from the DB for those with EST as the time zone so that they sum for the actual localized date? The post date for someone with EST selected as their time zone should be 12/23/07 and the calorie total, 230. This is what I''m using in my model: query_date = date + "%" Food.find_by_sql(["SELECT sum(calories) AS sum_calories FROM foods WHERE user_id = ? and submit_date like ?", user, query_date]) The model method only returns one of the two items obviously and I need it to return both for EST time zone users (for instance). Do i convert the dates to seconds to compare? How should this work? Many thanks in advance, this one has been frustrating me for 2 days and I''m sure it''s rather simple. -A --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 9 Feb 2008, at 20:16, ressister wrote:> > If these post dates were converted to Eastern Standard time, they both > have been posted on the same day, 12/23/07, which is how they''re being > displayed on the site for someone with EST time. > > How do i retreive these entries from the DB for those with EST as the > time zone so that they sum for the actual localized date? The post > date for someone with EST selected as their time zone should be > 12/23/07 and the calorie total, 230. > > This is what I''m using in my model: > > query_date = date + "%" > Food.find_by_sql(["SELECT sum(calories) AS sum_calories FROM foods > WHERE user_id = ? and submit_date like ?", user, query_date]) > > The model method only returns one of the two items obviously and I > need it to return both for EST time zone users (for instance). Do i > convert the dates to seconds to compare? How should this work? Many > thanks in advance, this one has been frustrating me for 2 days and I''m > sure it''s rather simple.You can do something like "WHERE CONVERT_TZ(submit_date, ''UTC'', ''EST'') = x" Or, you can do "WHERE submit_date >= day_start and submit_date < day_end" where day_start is a GMT representation of midnight on submit date in that time zone, and day_end is a gmt representation of the midnight of the following dayt (ie convert(submit date + 1 day) If you use the first then you need to make sure that mysql''s internal timezone databases are setup (or it won''t know what EST means). THe first also has the disadvantage that it won''t be able to use indices on submit_date. Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks Frederick, I''ll give option #2 a shot. I had been working on
something like this before I saw your response:
SELECT sum(calories) AS sum_calories FROM foods WHERE user_id = 1 and
submit_date between CONVERT_TZ(''2007-11-01 00:00:00'',
''+00:00'',
''-5:00'') and CONVERT_TZ(''2007-11-01
23:59:00'', ''+00:00'', ''-5:00'')
This approach doesn''t work, but the one you suggested should.
Thanks again, I''ll let you know how it goes.
-A
On Feb 10, 2008 1:13 AM, Frederick Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
>
> On 9 Feb 2008, at 20:16, ressister wrote:
>
> >
> > If these post dates were converted to Eastern Standard time, they both
> > have been posted on the same day, 12/23/07, which is how
they''re being
> > displayed on the site for someone with EST time.
> >
> > How do i retreive these entries from the DB for those with EST as the
> > time zone so that they sum for the actual localized date? The post
> > date for someone with EST selected as their time zone should be
> > 12/23/07 and the calorie total, 230.
> >
> > This is what I''m using in my model:
> >
> > query_date = date + "%"
> > Food.find_by_sql(["SELECT sum(calories) AS sum_calories FROM
foods
> > WHERE user_id = ? and submit_date like ?", user, query_date])
> >
> > The model method only returns one of the two items obviously and I
> > need it to return both for EST time zone users (for instance). Do i
> > convert the dates to seconds to compare? How should this work? Many
> > thanks in advance, this one has been frustrating me for 2 days and
I''m
> > sure it''s rather simple.
>
>
> You can do something like "WHERE CONVERT_TZ(submit_date,
''UTC'', ''EST'')
> = x"
> Or, you can do "WHERE submit_date >= day_start and submit_date <
> day_end"
> where day_start is a GMT representation of midnight on submit date in
> that time zone, and day_end is a gmt representation of the midnight of
> the following dayt (ie convert(submit date + 1 day)
>
> If you use the first then you need to make sure that mysql''s
internal
> timezone databases are setup (or it won''t know what EST means).
THe
> first also has the disadvantage that it won''t be able to use
indices
> on submit_date.
>
> Fred
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---