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

[Rob Thorne]

I've implemented a "proof of concept" time zone and date/time library.  
You can find a copy up at:

    http://torenware.com/bazaar/trunk/timezones/php_datetime/

The library's code is pretty simple, and all it does is the low level 
stuff:  turn Unix time stamps into formatted dates, and allow users to 
get the correct Unix time stamp given the time as known for their own 
local time zone.

To test it out:

    * Load the SQL tables (which are SQL versions of Olson's latest 2006
      zone data.)
    * Install the files in the "drupal" directory into your modules
      directory.
    * browse to YOUR_SERVER_BASE/timetest

The code is no frills, but it's passed at least a few tests.  It also 
does not depend on anything except the MySQL tables and a compatible 
version of Drupal and PHP.

I encourage folks to play with it.  I think it's simpler and easier to 
maintain than most other solutions I've seen, it covers any time zone or 
locality you're likely to think of, and it should be reasonably fast.

There isn't much Drupal-specific code in this either, so I suspect that 
it could be ported easily to other languages or PHP frameworks.

Cheers,
Rob
Rob Thorne wrote:
> 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.
>>>>       
>>
>>   
>
>