[development] Re-Thinking Events in Drupal (and time zones too)

[Rob Thorne]

One of the best defenses of using Unix timestamps has to do with 
handling daylight savings time/ summer time.  Correctly handling dates 
in time zones that have transitions during the year is very hard to do 
right unless you use Unix timestamps.

This is also why I'm not sure using database functions is the right 
approach;  we'll just be trading one set of problems for another.  But I 
think there's a way out of this.  I've spent some time over the last day 
or two looking at the Arthur Olsen library, and have a suggestion of how 
to solve the DST/ST problem in a general way.

The way the Olsen libraries work is that for every geopolitical unit in 
the world, there's only a few cases:

    * Either the unit's time is some constant offset in seconds from
      UTC/GMT (i.e., standard time all the time), or
    * Standard time has a some offset from UTC, and at a point in the
      year, you switch to summer time, and at another point in the year,
      you switch back.  Different countries do the shifts on different
      dates, and the northern hemisphere and the southern hemisphere
      obviously have summer time at opposite times of the year.

So Olson's solution is to compile all the various rules into a data file 
that tracks the exact Unix timestamp for every "transition" in the time 
zone.  The compiled objects let you do a binary lookup to find which 
"state" you're in at a given part of the year.  Once you know your 
"state", you look up the offset from UTC for that part of the year, and 
you know for every date of the year how many seconds you need to 
add/subtract from UTC to get local time.

The solution for us, I think, is to turn these binary lookup tables into 
PHP objects with the same information.  You store the transition times 
and offsets in the database.  When you need to calculate a date, load 
the data for that time zone from the database, and store away two 
arrays:  the presorted array of transition times, and a corresponding 
array of offsets from UTC for the period that starts with that Unix time 
stamp.  You only need to do this once per request per time zone, and you 
only need to do it for the time zones you use.

This will be pretty fast, and will be correct for any time zone that the 
Olson database knows about, which is pretty much everybody.  And even 
better: it doesn't depend upon PHP implementing this correctly, nor does 
it depend upon the services we get from a DB engine like MySQL or 
Postgresql.

I wrote a little perl script today that generates the needed SQL for 
Olson's current 2006 figures.  That's the only part that would be new to 
most of you.  I haven't written the code yet to do the calc, but once 
most of you look at the data, it will be pretty obvious how to do it.

I'll a bit more info about how to do this later.  But given how many 
countries were represented at our meeting in Vancouver, I think it's 
worth getting this right finally.

Cheers,
Rob Thorne
Torenware Networks


Khalid B wrote:
> I am confused too Adrian as to which one are you advocating?
>
> I have always wondered why we used UNIX timestamps while there
> are database native types for date and time.
>
> Over time, I got used to the timestamp things, and it does not
> bother me much anymore.
>
> I wrote some primitive routines to handle FormAPI to timestamp
> and vice versa.
>
> function fapi2date($date = array()) {
>   return strtotime($date['year'] . '-' . $date['month'] . '-' . $date['day']);
> }
>
> function date2fapi($date) {
>   if (!$date) {
>     $date = time();
>   }
>
>   $fapi_date = array();
>
>   $fapi_date['year']  = date('Y', $date);
>   $fapi_date['month'] = date('j', $date);
>   $fapi_date['day']   = date('d', $date);
>
>   return $fapi_date;
> }
>
> I still miss the ability to handle dates in the back end in queries though.
>
> On 3/5/06, Robert Douglass <rob at robshouse.net> wrote:
>   
>> So wait, you're confusing me. Which approach are you advocating? Db
>> handled date/times, or timestamp()/PHP handled???
>> Adrian Rossouw wrote:
>>     
>>> On 05 Mar 2006, at 11:22 AM, Bèr Kessels wrote:
>>>
>>>       
>>>> I think nearly all modules out there should try to rethink how / if
>>>> they would be if they were nothing more then bunch of a:
>>>>  Views and CCK definitions,  Views and CCK extensions and custom  logic.
>>>>         
>>> Yup.
>>>
>>> since we are doing the date type stuff now properly.
>>>
>>> how about we consider moving to actual in-database date fields  instead
>>> of using timestamps.
>>>
>>> i quite like being able to use the database for my date handling.  Truth
>>> be told, that is before
>>> I started using strtotime (which is by far the most useful function  in
>>> php's date handling lib),
>>> and was dissillusioned at how horrible mysql's date handling is
>>> compared to postgres.
>>>       
>
>