This is a great reference post! Thanks. Looking at the blogs about the 2.1
release I think rails2.1 has timezone support that follows your description,
i.e. one default way to store dates in the database (default UTC, but can be
changes in application.rb). I believe the timezone support includes DTS. We
have a user preference for setting the timezone you're currently in, so the
stored UTC-dates are converted to your current timezone. And there is
support for getting the current time on the server.
My last remaining question is rails related. How does rails handle the
following query
find(:all, :conditions => ['show_from <= ?', Time.now.utc ]).collect { |t|
t.activate! }
I know that Todo.show_from is mapped to the correct timezone, but I could
not find / google if the show_from in the :conditions is mapped to the
correct timezone.
I guess I need to set up some test code to find out :-)
Reinier
> -----Oorspronkelijk bericht-----
> Van: mathew murphy [mailto:[EMAIL PROTECTED] Namens mathew
> Verzonden: maandag 8 september 2008 18:15
> Aan: Reinier Balt
> CC: [email protected]
> Onderwerp: Re: [Tracks-discuss] ticket 747 and timezone issue
>
> Reinier Balt wrote:
> >
> > I agree that Todo.show_from is a Date converted to the user's
> timezone
> > (*). But does the time_zone in the :condition clause refer to the
> > database field (in UTC (**) ) or does it refer to Todo.show_from (in
> > the users time_zone)?
> >
> > I always thought activerecord just appends the :condition clause to
> > the SQL query and thus refers to the database field and not the
> > getter/setter of the object. Or am I wrong?
> >
> > Furthermore, you say "Basically, Times/Datetimes in the database are
> > in UTC, but Dates are not". I assume you mean UTC only when
> > config.active_record.default_timezone is UTC?
> >
>
> In general, there's only one way to do this correctly.
>
> [Let me start out by explaining my background: I've been building
> date/time and calendar code for most of my career, starting in 1990. I
> currently maintain multi-country enterprise calendar systems at a very
> big corporation, and have recently been doing a lot of systems
> integration of calendars using web services and Java.]
>
> Every time&date stored in the database must be stored in UTC, because
> not all SQL databases support time zones. While PostgreSQL will store a
> timestamp with time zone, MySQL and DB2 will not. So the code needs to
> work for the lowest common denominator.
>
> You might ask why not simply store database date/time values in local
> time zone. There are at least three good reasons why not...
>
> Firstly, the local time zone's definition may change, as is often the
> case with DST transitions.
>
> Secondly, the system may have users from multiple time zones, and when
> two or more time zones change you can get massive confusion.
>
> Thirdly, it means your time/date values are not unique. BSD syslog has
> this problem. For example, the timestamp instant 2007-10-30 01:55:47
> actually occurred twice, because of DST changeover. I've got a long
> post
> talking about this issue at
> <URL:http://meta.ath0.com/2005/05/25/syslog-is-broken-will-someone-
> please-fix-it/>
>
> So, in general the only safe thing to do is store date/time values in a
> single non-varying time zone which has a 1:1 mapping from date/time
> value to an instant in physical time. UTC is the obvious choice to meet
> those criteria. That means that the web application needs to translate
> all date/time values from UTC when loading them from the database, and
> back to UTC when storing them.
>
> Since there's no standard way to query a web client for the user's time
> zone, generally there will be an application default time zone which
> the
> application converts dates and times to for display and modification. I
> assume that's what the new Rails feature is. I'm guessing Rails
> automagically converts to UTC when persisting, and converts back when
> loading data.
>
> [To add an extra layer of complexity, for a multi-user application
> (like
> Tracks can be), you'll of course want to have a preference for each
> user's time zone, and convert date/time values to and from that instead
> of the server's/app's default time zone.]
>
> Now, there are a couple of problems with this approach, which become
> apparent when someone changes time zone--either because of a DST
> changeover, or because they've traveled to a place with a different
> time
> zone, or perhaps because the time zone has been redefined by
> legislature.
>
> The first problem is what a date with no time zone means. The answer is
> that in general, people expect "2008-09-08" to behave as
> "2008-09-08T00:00:00--2008-09-08T24:00:00" *in whatever time zone they
> happen to be in at the time*.
>
> That is, if I set a date of 2008-09-08 for something when I'm in Texas,
> and then I fly to Germany, I expect the thing to happen on 2008-09-08
> in
> Germany, even though I had no idea I would be in Germany when I set the
> date. And when I looked at the calendar in January, I wanted to see the
> thing appear on a single day, as if it was scheduled in the time zone I
> was in in January.
>
> To make the system behave this way, you basically need to perform no
> time zone skew if there's no time component to a date/time value.
> Instead, you simply append the application or client time zone
> information, and any implicit time range (e.g. 00:00:00--24:00:00).
> I've
> no idea if that's what Rails does, but it's what it should do. :-)
>
> The second problem is probably not going to affect tracks, but it's the
> problem of how you deal with events that are scheduled for a particular
> time range specified in local time, if the user switches time zone
> after
> scheduling the event. The answer in this case is that *there is no
> correct answer*.
>
> Example 1: I have a weekly telephone conference call at 10:00 on
> Tuesday. I happen to be working in Germany for a week. Clearly my
> calendar appointment needs to appear at 17:00 local time while I'm in
> Germany.
>
> Example 2: While I'm in Germany, I'm going to be meeting with
> colleagues
> at 10:00 on Wednesday. Clearly my calendar appointment needs to appear
> at 10:00 local time while I'm in Germany.
>
> So the only way to deal with this latter problem in general, is for
> each
> calendar event to have either a controlling time zone associated with
> it, or "Local" to indicate that it should float to whatever the user's
> local time zone is at any given moment (like with an untimed date, as
> per previous discussion). Unfortunately, there are a lot of calendar
> systems that don't provide this feature; e.g. Palm organizers and
> smartphones. And unfortunately, there's no standard way to store such
> information in a relational database; I had to do a lot of code
> refactoring recently when switching from PostgreSQL to IBM DB2.
>
> I hit this problem because one system I maintain is used to schedule
> events at corporate locations. When an event at a physical location is
> scheduled for 10:00 on August 9th, what is actually meant is "10:00 in
> the local time for that location on August 9th, whatever that time zone
> happens to be, even if the government redefines DST earlier in the year
> to be something you didn't expect". :-)
>
> Anyway, rather a long e-mail, but hopefully it'll help clarify the
> issues.
>
>
> mathew
_______________________________________________
Tracks-discuss mailing list
[email protected]
http://lists.rousette.org.uk/mailman/listinfo/tracks-discuss
