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
On 9 Feb 2008, at 20:16, ressister wrote:
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
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