CREATE FUNCTION "DateAdd" (
IN "DatePart" VARCHAR COLLATE "UNI_CI",
IN "Number" INTEGER,
IN "Value" TIMESTAMP)
RETURNS TIMESTAMP
BEGIN
 DECLARE Result TIMESTAMP;
 DECLARE Year, Month, Day, Hour, Min, Sec, MSec INTEGER;

 SET Year=EXTRACT(YEAR FROM Value);
 SET Month=EXTRACT(MONTH FROM Value);
 SET Day=EXTRACT(DAY FROM Value);
 SET Hour=EXTRACT(HOUR FROM Value);
 SET Min=EXTRACT(MINUTE FROM Value);
 SET Sec=EXTRACT(SECOND FROM Value);
 SET MSec=EXTRACT(MSECOND FROM Value);

 IF (DatePart='MILLISECOND') OR (DatePart='MS') THEN
   SET MSec=MSec+Number;
 ELSE
   IF (DatePart='SECOND') OR (DatePart='SS') OR (DatePart='S') THEN
     SET Second=Second+Number;
   ELSE
     IF (DatePart='MINUTE') OR (DatePart='MI') OR (DatePart='N') THEN
       SET Minute=Minute+Number;
     ELSE
       IF (DatePart='HOUR') OR (DatePart='HH') THEN
         SET Hour=Hour+Number;
       ELSE
         IF (DatePart='DAY') OR (DatePart='DD') OR (DatePart='D') THEN
           SET Day=Day+Number;
         ELSE
           IF (DatePart='MONTH') OR (DatePart='MM') OR (DatePart='M') THEN
             SET Month=Month+Number;
           ELSE
             IF (DatePart='YEAR') OR (DatePart='YYYY') OR (DatePart='YY') THEN
               SET Year=Year+Number;
             ELSE
             END IF;
           END IF;
         END IF;
       END IF;
     END IF;
   END IF;
 END IF;

 SET Result = EncodeTimeStamp(Year, Month, Day, Hour, Min, Sec, MSec);
 RETURN Result;
END

One would use DateAdd to add some value to portion of a decoded date.
For example, given a timestamp Value of 2012-02-06 15:48:00.123, you could:

- add 4 minutes: DateAdd('MINUTE', 4, Value);
- or add 2 days: DateAdd('DAY', 2, Value);
etc.

This is a simplified version of some MS SQL DateAdd I found.

I think one of Murphy's Computer Laws states that as soon as you write a
work-around for a bug or deficiency in piece of software, an update comes
out rendering your code obsolete. Such may be the case now that you've
discovered the wonderful world of ElevateDB.  The INTERVAL operator works
like this (using your two examples):

- add 4 minutes: Value + INTERVAL '4' MINUTE
- add 2 days: Value + INTERVAL '2' DAY


David Cornelius
Cornelius Concepts

> add 4 minutes: Value + INTERVAL '4' MINUTE
> add 2 days: Value + INTERVAL '2' DAY

-), yeah! I'm still not familiar with the INTERVAL stuff.

Thanks,
Lucian