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