Op vrijdag 3 maart 2006 21:29, schreef Adrian Rossouw:

I personally think that we should focus all this event api related   discussion into writing a proper date / time field for CCK, as all that code will be re-used by the forms system in the next   release.

Additionally views should be able to do event based views (if it   doesn't already. it does like. everything).

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. IMO that is the future of contribs :) Bèr

So wait, you're confusing me. Which approach are you advocating? Db handled date/times, or timestamp()/PHP handled??? Dude, we can't continue to worship you if you're ambiguous ;-) 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.

-- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

Op zondag 5 maart 2006 19:27, schreef Khalid B:

I still miss the ability to handle dates in the back end in queries though.

db_date_str2db() //API to convert a string to the database-engine specific/preferred date format db_date_db2str() //API to convert a string from a db-eninge specifc date to a common Drupal date. And an automagically converting %t: use as db_query("INSERT INTO {foo} SET (time='%t')", $node->accesstime); db_query("SELECT nid FROM {foo} WHERE time > %t'", $node->accesstime); Just a brainstormy idea, nothing more :) Bèr Kessels -- | Bèr Kessels | webschuur.com | website development | | Jabber & Google Talk: ber@jabber.webschuur.com | http://bler.webschuur.com | http://www.webschuur.com | Sympal draait nu voor het grootste deel al op 4.7: http://help.sympal.nl/sympal_draait_nu_voor_het_grootste_deel_al_op_4_7

When I said "I still miss the ability to handle dates in the back end in queries though." I meant what Adrian has summed up nicely here:

So in conclusion, timestamps only become a problem for me when working with intervals, and then only when the intervals are not a constant amount of seconds (ie: x months or even years if you consider leap years). I am pretty sure that's enough of an edge case to let it slide, but there might be other benefits to using date types that i don't know about.

The ability to do the range/interval check in the database is nicer than retrieving the whole data set in PHP and filtering it there. However, as he said: MySQL and PostrgreSQL do it with different syntax, so there is no straight forward solution to this.

Adrian Rossouw wrote:

Due to the fact that we use timestamps, this gets a bit more complex, as we need to do a whole lot of casting.

Mysql : update sometable set next_bill_date = unix_timestamp(date_add (form_unixtime(next_bill_date), interval 1 month))) where cid in ();

Postgres : update sometable set next_bill_date = extract(epoch from (TIMESTAMP WITH TIME ZONE 'epoch' + created * interval '1 second) + '1 month')::int where cid in ();

What complicates matters is how drastically different postgres and mysql handle dates, and the thing is in our specific use case, I'm not sure what benefits database field types would give us without adding a whole lot of possibly unnecessary complexity.

IIRC this was the argument that lead to us to keep timestamps for events after lenghty discussions in Antwerp one year ago. I still don't like it. :p Cheers, Gerhard

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.

On 3/6/06, Adrian Rossouw <adrian@bryght.com> wrote:

On 06 Mar 2006, at 11:37 AM, Rob Thorne wrote:

...

One of the best defenses of using Unix timestamps has to do with handling daylight savings time/ summer time.

complete aside and only mostly off topic :

Am I the only person who thinks that timezones and especially daylight savings time are completely moronic inventions.

It would just be so much simpler if everyone operated on GMT.

I also wonder, that if one day when we end up living on other planets, are we going to insist on basing all our time keeping devices on the speed that some other celestial body spins on it's axis and orbits around some other celestial body.

The entire idea of a decimal time system also appeals to me, and why i really liked the idea of swatch internet time. http://en.wikipedia.org/wiki/Decimal_time

But then again, when I was watching the animatrix and the robots named their first city '01' I was like "err. shouldn't that be 00"

Timezones are a pain. The US just enacted new rules to extend daylight savings time by two months every year, and will go in effect this year (I think). Ontario followed suite saying lots of our customers are in the USA, but complicating things for out interaction with the rest of the world. On the other hand, timezones tell you something that decimal time or Swatch internet time would not: they give you an idea whether the people you are trying to call are awake, at work or not. The decimal/internet time would not tell me that, and it has to be looked up. In other words, a city can be at astronomical noon at time 2345, yet another has noon at 0934 (numbers are made up). In any case, this is a reality forced on us, and it is not about to change any time soon. Perhaps the powers that be will realize that with a more connected world, something has to be done, and take steps to do it.

On 3/6/06, Gunnar Langemark <gunnar@langemark.com> wrote:

Khalid B wrote:

...

In any case, this is a reality forced on us, and it is not about to change any time soon. Perhaps the powers that be will realize that with a more connected world, something has to be done, and take steps to do it.

So let's see some of the renowned Drophead geniuses solve it! There must be a business model for this. 2c for every time anybody in the world uses "Drupalmanac" time would generate more $$$ than Bill Gates has - in just a few days (drops).

Has been tried before and never took off, even without asking for money. http://en.wikipedia.org/wiki/Swatch_Internet_time

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.

It is even hard using unix timestamps. Look at the bugs logged for event module, there is apparently still some inconsistency lurking in the code (or maybe people don't read the instructions).

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.

Doing so will be quite hard. i just want to remind people of my pending patch here: http://drupal.org/node/11077 Anyway, I don't see why implementing DST would be harder with proper date formats than using timestamps. Adding or substracting one hour should be easy enough if you know which DST applies.

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.

My rather simple patch covers a largish part of the earth's landmass. Cheers, Gerhard P.S.: Can you please educate your MUA to not cite the whole thread? Thanks.

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.

Well! This is my area of interest! I'll be there with bells on. I agree the date/time stuff needs to be designed properly. No one has responded to my issue post http://drupal.org/node/47528 yet. I'd like to chat with someone about that stuff. See you at the meeting! -- Sammy Spets Synerger Pty Ltd http://www.synerger.com/ On 03-Mar-06 22:29, Adrian Rossouw wrote:

On 03 Mar 2006, at 10:21 PM, Moshe Weitzman wrote:

...

we just re-thought events! ask crunchywelch about it. he hid in hole while the whole civicspace community breathed hot flames at him until it was done.

I personally think that we should focus all this event api related discussion into writing a proper date / time field for CCK, as all that code will be re-used by the forms system in the next release.

Additionally views should be able to do event based views (if it doesn't already. it does like. everything).

-- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

I find that repeating events is one of the strong points of the Palm OS calendar app. It's been a LONG time since I did Palm development, but the original Date Book app used to have its source code available as example code. Perhaps that's something we'd want to look at for inspiration. On Sunday 05 March 2006 07:27, Grant Malcolm wrote:

*first post*

Sadly, the IRC timing's really lousy for folks in australia.

;-)

So will I be excused if I stick in an email plug for catering for non-contiguous multi-day events?

This is a particular requirement for some types of events; e.g. performances: essentially the same event repeated over several weeks but not necessarily on every day.

http://home.it.net.au/~gmalcolm/?q=node/18872

I added an extra table event_days

mysql> describe event_days; +-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| nid | int(10) | YES | | NULL | | | date | int(10) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

where there is a row for every date that nid (the event) occurs. Timezone isn't an issue with this example but you could probably just use event.tz offset if it were. It's not visible without a login, but I ended up opting for an utterly inaccessible js tool (http://www.calendarxp.net/tt_flat.shtml) for inputting dates rather than the couple of months' worth of check boxes I used to display.

It's a hack and I'm yet to clean up day, week, month views to use occurences of date rather than start and end.

If I was clever, and I'm not, I'd have modified the module to add a flag you can set (for each event?) to define whether it's repeating, multiday, non-contiguous.... anyway, I hope it gives someone some ideas.

Cheers Grant

-- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson