# Selecting all events on a given day with time zone sensitivity

**URL:** <https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247>\
**Category:** Rails\
**Created:** [August 7, 2008, 5:59am UTC](https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247 "2008-08-07T05:59:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![brendon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/5f9b8f/32.png) [@brendon](https://www.ruby-forum.com/u/brendon)\
**Post date:** [August 7, 2008, 5:59am UTC](https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247/1 "2008-08-07T05:59:24Z")

</div>

Hi everyone, just grappling with converting app to being time zone  
sensitive.

I have a calendar and you can click on a day to see all the events on  
a given day. The easy way to do this before time zones was just to  
pass the date into the sql query and compare it with  
date(event\_datetime) in straight sql. But now consider that an event  
has been created in the default time zone which happens to be quite  
far forward of UTC (Auckland in our case), and then we go to find the  
events on the day we thought we saved this event on. Because the  
database is storing the event on an ‘earlier’ day in UTC (in some  
cases), our simple comparison will never work. I realise that I’ll  
need to be asking mysql to return events within a given range now and  
I need that range to be automatically adjusted from the current time  
zone (e.g. 2/1/2008 00:00:00 - 2/1/2008 23:59:59) to the UTC timezone  
(which in our case is 1/2 a day backward). I’ve tried creating dates  
using DateTime.new but these seem to be created in UTC (the offset is  
+00:00) and so when you go .to\_s(:db) they end up the same.

Has anyone struggled with this before and got further than me?

Cheers,

Brendon

---

<div class="post-metadata">

**Author:** ![brendon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/5f9b8f/32.png) [@brendon](https://www.ruby-forum.com/u/brendon)\
**Post date:** [August 7, 2008, 9:00am UTC](https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247/2 "2008-08-07T09:00:39Z")

</div>

On Aug 7, 4:58 am, Brendon [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> database is storing the event on an ‘earlier’ day in UTC (in some  
> cases), our simple comparison will never work.

The way I do it is convert the range I’m looking for from local into  
UTC, and use that time in the various searches. I use the tzinfo gem  
for the actual conversion stuff, so for example you can do something  
like

time\_zone = TZInfo::Timezone.get ‘Europe/London’  
time\_zone.utc\_to\_local(some\_time)

on the 2 end points of your search.

I actually include a module into time so that I can do to\_local/  
from\_local on instances of Time which saves some repetition.

Fred

---

<div class="post-metadata">

**Author:** ![brendon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/5f9b8f/32.png) [@brendon](https://www.ruby-forum.com/u/brendon)\
**Post date:** [August 8, 2008, 12:02am UTC](https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247/3 "2008-08-08T00:02:20Z")

</div>

Thanks Fred,

You’re right, I managed to do it yesterday after writing the post and  
poking around the Rails API. One big pitfall is that (using the new  
rails 2.1 tz support) DateTime.new doesn’t give you a timezone  
adjusted time (aka NOW in the currently selected time zone), it gives  
you a blank DateTime which took me a while to figure out.

Anyway, in the end this was my query, I added it to the calendar class  
so that a calendar can find its events in a given day, month, or year.  
The code is probably not that great but it works:

def events\_in(period, time)  
case period  
when :day  
conditions = [‘datetime \>= ? AND datetime \<= ?’,  
time.beginning\_of\_day.to\_s(:db), time.end\_of\_day.to\_s(:db)]  
when :month  
conditions = [‘datetime \>= ? AND datetime \<= ?’,  
time.beginning\_of\_month.to\_s(:db), time.end\_of\_month.to\_s(:db)]  
when :year  
conditions = [‘datetime \>= ? AND datetime \<= ?’,  
time.beginning\_of\_year.to\_s(:db), time.end\_of\_year.to\_s(:db)]  
end

```
if conditions
  events = self.calendar_events.find(:all, :conditions =>

```

conditions, :order =\> ‘datetime’)  
events += self.shared\_events.find(:all, :conditions =\>  
conditions, :order =\> ‘datetime’)  
events.sort!{ |x,y| x.datetime \<=\> y.datetime }  
else  
events = nil  
end

events  
end

There’s some bits in there that are superfluous to most people as we  
have linked in events from other calendars. If anyone has any  
suggestions on how this could be coded better please let me know. I’m  
eager to learn 🙂

Cheers,

Brendon

On Aug 7, 6:59 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![brendon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/5f9b8f/32.png) [@brendon](https://www.ruby-forum.com/u/brendon)\
**Post date:** [August 8, 2008, 12:39am UTC](https://www.ruby-forum.com/t/selecting-all-events-on-a-given-day-with-time-zone-sensitivity/145247/4 "2008-08-08T00:39:11Z")

</div>

Oh also, another way (Which i used above) to get a time in UTC is to  
call .to\_s(:db) on the time.

Cheers,

Brendon