# Summing up data by user-selected time zone

**URL:** <https://www.ruby-forum.com/t/summing-up-data-by-user-selected-time-zone/128646>\
**Category:** Rails\
**Created:** [February 9, 2008, 9:17pm UTC](https://www.ruby-forum.com/t/summing-up-data-by-user-selected-time-zone/128646 "2008-02-09T21:17:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ressister](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/258eb7/32.png) [@ressister](https://www.ruby-forum.com/u/ressister)\
**Post date:** [February 9, 2008, 9:17pm UTC](https://www.ruby-forum.com/t/summing-up-data-by-user-selected-time-zone/128646/1 "2008-02-09T21:17:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ressister](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/258eb7/32.png) [@ressister](https://www.ruby-forum.com/u/ressister)\
**Post date:** [February 10, 2008, 7:13am UTC](https://www.ruby-forum.com/t/summing-up-data-by-user-selected-time-zone/128646/2 "2008-02-10T07:13:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ressister](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/258eb7/32.png) [@ressister](https://www.ruby-forum.com/u/ressister)\
**Post date:** [February 11, 2008, 5:24am UTC](https://www.ruby-forum.com/t/summing-up-data-by-user-selected-time-zone/128646/3 "2008-02-11T05:24:07Z")

</div>

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