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
