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
