[development] Re-Thinking Events in Drupal

[Adrian Rossouw]

On 05 Mar 2006, at 11:57 PM, Gerhard Killesreiter wrote:

>>
>> 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 ();
I was just thinking about this, and came up with the following for  
postgres :

create or replace function from_unixtime(integer) returns timestamp as '
   select TIMESTAMP WITH TIME ZONE 'epoch' + $1 * INTERVAL '1 second';
' language 'sql';

create or replace function unix_timestamp(timestamp) returns integer  
as '
   select extract(epoch from $1)::int;
' language 'sql';

apart from date_add not being the same, atleast the conversion matches.

so now the postgres example would be :

update sometable set next_bill_date = unix_timestamp(from_unixtime 
(next_bill_date) + '1 month') where cid in ();

still. i'd prefer not having to mess around with casting in the first  
place.

I don't know if making our own version of date_add is going to be  
simple.
date_add(next_bill_date, interval 1 month) is invalid, as postgres is  
expecting interval '1 month'

it would have to be a db rewrite query thing =(

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