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@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.