Selecting all events on a given day with time zone sensitivity

rubyonrails-talk
1

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

2

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

3

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 :slight_smile:

Cheers,

Brendon

4

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