Roberto has a function for adding and subtracting dates at:
www.brasileiro.net
/postgres/cookbook/
Here is the function to date_add.
CREATE FUNCTION DATE_ADD(date,int4,text) RETURNS DATE AS '
DECLARE
var1 date;
var2 text;
BEGIN
var2 = $2 || '' '' || $3;
SELECT INTO var1
to_date($1 + var2::interval, ''YYYY-MM-DD'');
RETURN var1;
END;' LANGUAGE 'plpgsql';
Here are some issues that I'd like some help with.
It works ok when I use the example:
select date_add('1999-12-31',1,'DAYS') from dual;
2000-01-01
-- OK
select date_add('1999-12-31',1,'YEARS') from dual;
2000-01-31
-- WRONG, should be 2000-12-31.
-- It only goes ahead 1 month
select date_add('1999-12-31',1,'MONTHS') from dual;
2000-01-31
-- OK but shouldn't it add only 30 days?
select date_add('1999-12-31',12,'MONTHS') from dual;
2000-01-31
-- WRONG!
-- It should be 2000-12-31 and go ahead 12 Months
OK, You get the idea.... It's not working..... ALSO it does
not take into acccount the whole date-time value.
What I want to do, is take a date from the database
and add exactly day(s), month(s) or years(s) then stuff
it back into the database, so for example:
regis_date = 2002-03-15 09:17:46-05
I want to stuff anniv_date with 1 year from now:
update jtest
set anniv_date=DATE_ADD(regis_date '03/15/2002',1,'YEARS') where...
...
select anniv_date ...
2003-03-15 09:17:46-05
Suggestions for fixing Roberto's function or even another method
to do the same would be appreciated.
Separately, how can 'epoch' date-time
add/sub seconds and stuff it back:
select date_part('epoch', now()) from dual;
------------
1016203837
update jtest set insert_date=date_part('epoch', now()) where...
ERROR: Attribute 'insert_date' is of type 'timestamp'
but expression is of type 'float8'
You will need to rewrite or cast the expression
How?
Thanks
-Bob