How can I add a variable x days to an TIMESTAMP, where the X must be a variable inside a function?

(This is a simplified example)
CREATE FUNCTION "AddDays" (IN "ATime" TIMESTAMP, IN ADays INTEGER)
RETURNS TIMESTAMP
BEGIN
 RETURN ATime + INTERVAL ADays Day;
END

This doesn't work as INTERVAL expects a constant. So how cab we use intervals inside a funcitron or procedure, where we have variable interval values?

Rolf


From an earlier post by Fernando Dias

CREATE FUNCTION "ReturnBestBeforeDate" (IN "aProductionDate" DATE, IN "aShelfLife" INTERVAL DAY)
RETURNS DATE
BEGIN
  RETURN aProductionDate + aShelfLife ;
END

Freaky aint it


Roy Lambert

This dioesn't work in may real case. As the number of days is caclulated inside my function.

This doesn't work:

CREATE FUNCTION "AddDays" (IN "ATime" TIMESTAMP)
RETURNS TIMESTAMP
BEGIN
 DECLARE Days INETERVAL DAYS;  -- was initial a INTEGER
 SET Days = 5;   -- this 5 is in real a calculated variable and NOT a constant

 RETURN ATime + Days;
END

Rolf

ALTER FUNCTION "AddDays" (IN "ATime" TIMESTAMP)
RETURNS TIMESTAMP
BEGIN
 DECLARE Days INTERVAL DAY;  -- was initial a INTEGER
 DECLARE CalcDays INTEGER DEFAULT 0;

SET CalcDays = RAND(82);

 SET Days = CAST (CalcDays AS INTERVAL DAY);   -- this 5 is in real a calculated variable and NOT a constant

 RETURN ATime + Days;
END

Just alter the RAND(82) with your calculation

Roy Lambert

Ok but it is a little bit more complicated. So I will write what I realy want to do.

I have x Days (INTEGER) and also a Time (TIME) value. I want now combine the two to get an interval Day to MSeconds. My Time may be "01:35:10.456" and the days value is 5. Now I want get an interval from that like this "5 01:35:10.456". I have still not found a solution to do this yet.

Rolf

Can you post code to show what you want to achieve. Its still unclear from what you've given so far. Some indication as to how the function is to be used would also be helpful.

Roy Lambert

Tanks Roy, I was now able to find a solution, but it's strange to use so much castings. I'm a littlebit disapointed that EDB does have such a poor date/time functionality and is missing much of utility functions. Here is a list of function I want do see built in:

EncodeTime
EncodeDate
EncodeTimeStamp
A simple function to combine a TIME and a DATE field into a TIMESTAMP
A simple function to EXTRACT the TIME and DATE part from a TIMESTAMP
Function to add/subtract two TIMESTAMPS / DATE / TIME
A NULL TIMESTAMP function which returns the lowest available TIMESTAMP. ('1901-01-01 00:00:00.000' ???)

What meks me unhappy is also, that a CAST(TimeField AS TIMESTAMP) returns the acutal date as the DATE part instead the NULL Date.

Also I was wondering, that there is no MOD function in EDB, as I was in need to have it in my function.

Here are my 3 function I did to get Delphi like result, that means it returns a FLOAT which then can be used to sum and other function which don't work with built ind date/time types, What still is missing is a function which produces a correct date from a days values from that FLOAT. For this I need a NULL TIME to which I can add the days to get the right Date/Timestamp. So what is the EDB representation of 0 days ? '1901-01-01 00:00:00.000'?

--- This routine converts a TIME into a Fraction (0.xxxxx) ---
CREATE FUNCTION "TimeToFraction" (IN "ATime" TIME)
RETURNS FLOAT
BEGIN
RETURN
 (EXTRACT(HOUR FROM ATime) / 24.0) +
 (1.0 / 1440.0 * EXTRACT(MINUTE FROM ATime)) +
 (1.0 / 86400.0 * EXTRACT(SECOND FROM ATime)) +
 (1.0 / 86400000.0 * EXTRACT(MSECOND FROM ATime));
END

--- This routine converts a fraction into a TIME ---
CREATE FUNCTION "FractionToTime" (IN "ATimeFraction" FLOAT)
RETURNS TIME
BEGIN
 DECLARE MSecsPerDay INTEGER DEFAULT 86400000;
 DECLARE MSecsPerHour INTEGER DEFAULT 3600000;
 DECLARE MSecsPerMinute INTEGER DEFAULT 60000;
 DECLARE MSecsPerSecond INTEGER DEFAULT 1000;
 DECLARE Hours INTEGER;
 DECLARE Minutes INTEGER;
 DECLARE Seconds INTEGER;
 DECLARE MSeconds INTEGER;
 DECLARE LTemp BigInt;
 DECLARE LTemp2 BigInt;

 SET LTemp = ROUND((ATimeFraction - TRUNC(ATimeFraction)) * MSecsPerDay);
 SET LTemp2 = ABS(LTemp) / MSecsPerDay;    -- MOD is Missing here to have one single line
 SET LTemp2 = ABS(LTemp) - (LTemp2 * MSecsPerDay);

 SET Hours = TRUNC(LTemp2 / MSecsPerHour);
 SET LTemp2 = LTemp2 - (Hours * MSecsPerHour);

 SET Minutes = TRUNC(LTemp2 / MSecsPerMinute);
 SET LTemp2 = LTemp2 - (Minutes * MSecsPerMinute);

 SET Seconds = TRUNC(LTemp2 / MSecsPerSecond);
 SET LTemp2 = LTemp2 - (Seconds * MSecsPerSecond);

 SET MSeconds = LTemp2;

 RETURN
   CAST(
     RIGHT('00' + RTRIM(CAST(Hours AS VARCHAR(2))), 2) + ':' +
     RIGHT('00' + RTRIM(CAST(Minutes AS VARCHAR(2))), 2) + ':' +
     RIGHT('00' + RTRIM(CAST(Seconds AS VARCHAR(2))), 2) + '.' +
     RIGHT('000' + RTRIM(CAST(MSeconds AS VARCHAR(3))), 3)
   AS TIME);
END


--- This routine converts a "Duration" Time (SUM of TIME) and converts it to an INTERVAL DAY TO MSECOND --
CREATE FUNCTION "FractionToTimeInterval" (IN "ATimeFraction" FLOAT)
RETURNS INTERVAL DAY TO MSECOND
BEGIN
 DECLARE MSecsPerDay INTEGER DEFAULT 86400000;
 DECLARE MSecsPerHour INTEGER DEFAULT 3600000;
 DECLARE MSecsPerMinute INTEGER DEFAULT 60000;
 DECLARE MSecsPerSecond INTEGER DEFAULT 1000;
 DECLARE Days INTEGER;
 DECLARE Hours INTEGER;
 DECLARE Minutes INTEGER;
 DECLARE Seconds INTEGER;
 DECLARE MSeconds INTEGER;
 DECLARE LTemp BigInt;
 DECLARE LTemp2 BigInt;
 DECLARE TmpTime TIME;

 SET Days = TRUNC(ATimeFraction);
 SET TmpTime = FractionToTime(ATimeFraction - Days);

 SET Hours =  EXTRACT(HOUR FROM TmpTime);
 SET Minutes = EXTRACT(MINUTE FROM TmpTime);
 SET Seconds = EXTRACT(SECOND FROM TmpTime);
 SET MSeconds = EXTRACT(MSECOND FROM TmpTime);

 RETURN
   CAST(CAST(Days AS VARCHAR(12)) + ' ' +
     RIGHT('00' + CAST(Hours AS VARCHAR(2)), 2) + ':' +
     RIGHT('00' + CAST(Minutes AS VARCHAR(2)), 2) + ':' +
     RIGHT('00' + CAST(Seconds AS VARCHAR(2)), 2) + '.' +
     RIGHT('000' + CAST(MSeconds AS VARCHAR(3)), 3)
   AS INTERVAL DAY TO MSECOND);
END

FractionToTimeInterval can be used to sumarize a duration field (TIME) as follow:
SELECT FractionToTimeInterval (SUM(TimeToFraction(Duration))) FROM PlayTimes

This will return an interval of '322 12:44:02.123'.

Rolf

>Tanks Roy, I was now able to find a solution,

Good

>but it's strange to use so much castings.

Without working it through I wouldn't know. What I do know is that the standards team want nasty things done to them for INTERVAL

>I'm a little bit disapointed that EDB does have such a poor date/time functionality and is missing much of utility functions. Here is a list of function I want do see built in:

I have no idea what the standard has defined in terms of utility functions so I don't know how well ElevateDB matches up. I felt the same about SOUNDEX which I needed so I wrote a "massive" utility routine, so massive it was just a line in the calling routine

else if ckn = 'soundex' then begin
 if ParamValues.FindByName('sLength').Null
  then ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, 4)
 else ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, ParamValues.FindByName('sLength').AsInteger)

ie call the Delphi routine

External routines are a major strength of ElevateDB and a lot of what you want could be easily (or more easily) achieved in Delphi.

I am fortunate in that I've not had to use INTERVAL DAY TO MSECOND or any of the similar constructs and I wouldn't be overly sure just what '322 12:44:02.123' was or how to deal with it. I assume its stored somewhere as an integer of some unit (maybe milliseconds) but as I say I've been lucky

Roy

ps


MOD is there - look in the OLH

Roy Lambert

OK found it now. I searched it under numeric functions and it isn't listed there. It seems to be a operator in EDB and not a function anymore as in DBISAM.