On 05 Mar 2006, at 8:27 PM, Khalid B wrote:
I am confused too Adrian as to which one are you advocating? Neither.
I was saying i love doing it in the database, but it's significantly less painful and easier to understand when using the strtotime function in php. We need to research the costs and benefits of switching to date types, especially regarding the database api. A lot of the stuff that I end up needing to do with dates has to do with intervals (ie: mail the user a reminder 5 days after his invoice was sent. or you want a view showing only the last 3 months of user posts) and for that I have found that I typically prefer doing it in the database. Say you need to for some reason give a free month to a whole bunch of clients. You can do that by (in mysql) : update sometable set next_bill_date = date_add(next_bill_date, interval 1 month)) where cid in (); and in postgres : update sometable set next_bill_date = next_bill_date + '1 month' where cid in (); To do the same in php would involve loading up each of the records and updating them individually. Since some clients might already have 3 months paid ahead and you can't give them X seconds free , it actually has to be an interval calculation. 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. 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. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com