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:
Post date: 2007-12-23 20:15:00
Post date: 2007-12-24 01:15:00
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.