Selecting all events on a given day with time zone sensitivity

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

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

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

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