[development] Re-Thinking Events in Drupal

[Adrian Rossouw]

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