Forum OpenACS Q&A: date_add, returns "wrong" dates

Collapse
Posted by MaineBob OConnor on

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

Collapse
Posted by Jonathan Ellis on
what version of PG are you using?  works fine for me in 7.1.3.

try it without the date_add wrapper --
select '1999-12-31'::date + '1 years'::interval;

(and yes, 12-31 + 1 month = 1-31 not 1-30)

Collapse
Posted by MaineBob OConnor on

Bob, why are you making it so complicated!
Great Jonathan, this works:

update jtest set 
insert_date =now()+'1 years'::interval where...
  1. As to why the wrapper doesn't work, it may be due to some date setting euro vs american dates???
  2. I'm using: psql 7.1.2

  3. You say, "(and yes, 12-31 + 1 month = 1-31 not 1-30)"
    And is this correct?: 12-31 + 2 month = 2-31 Hmmmm. or 3-3

  4. And this does not work:
    update jtest set 
    insert_date =insert_date +'20000 epoch'::interval where...
    ERROR: Bad interval external representation '20000 epoch'

    -Bob

Collapse
Posted by MaineBob OConnor on
Ok on #3 PG sez<br>
12-31 + 2 month = 2-28
Collapse
Posted by Jonathan Ellis on
not sure what you're trying to do with the epoch business.  if you want to add X seconds, add 'x seconds'::interval.
Collapse
Posted by MaineBob OConnor on
Great, Johathan: epoch = seconds...
just what I needed.
-Bob
Collapse
Posted by Roberto Mello on
Bob,

I hope you realized that I didn't write that function. In fact, I only have a few functions in the cookbook. They are functions contributed by other people. You should write the author about it and point him to this thread, in the spirit of free software.

I'm going to "port" my personal site do OpenACS 4.5 and re-write the cookbook, probably as an ETP application so submitters can update their functions and other people can submit improvements/comments.

Collapse
Posted by Jonathan Ellis on
I suspect something's screwed on Bob's end, though, because date_add works for me and it's so simple I can't see how it could be screwing up.
Collapse
Posted by MaineBob OConnor on

It was at my end, I did a:
drop function date_add (date, integer, text);

And again pasted the whole function into psql and it works as advertized... The only thing i can think of is the first time I loaded the function:
LANGUAGE 'plpgsql';
as a separate line... ??? Oh well...

AND I have written to Mark Steele (msteele@inet-interactif.com) the original author, (Thanks Roberto) and suggested that the function be improved to pass forward the hours rather then make it midnight when using this function to update fields:
update jtest set insert_date=date_add insert_date,1,'DAYS')...;

-Bob

Collapse
Posted by Mark Steele on
To get better precision to handle hours/minutes/seconds, you simply
need to modify the original functions and change
'YYYY-MM-DD' to 'YYYY-MM-DD HH:MI:SS'

This will probably preserve the hours/minutes/seconds of the original
date. If you want absolute precision, you'll need to do something fancier.

I haven't tested this out yet, but I am pretty sure it will work.
see http://www.postgresql.org/idocs/index.php?functions-formatting.html for more details on how the to_date function works.

Cheers