Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 30 total |
How to add variable x days to a TIMESTAMP? |
Sat, Nov 25 2017 1:07 PM | Permanent Link |
Rolf Frei eicom GmbH | 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? |
Sun, Nov 26 2017 2:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Nov 27 2017 7:53 AM | Permanent Link |
Rolf Frei eicom GmbH | 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 |
Mon, Nov 27 2017 10:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Nov 27 2017 12:10 PM | Permanent Link |
Rolf Frei eicom GmbH | 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. |
Tue, Nov 28 2017 3:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Nov 28 2017 8:22 AM | Permanent Link |
Rolf Frei eicom GmbH | 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'. |
Tue, Nov 28 2017 9:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Nov 28 2017 9:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | ps
MOD is there - look in the OLH Roy Lambert |
Tue, Nov 28 2017 9:10 AM | Permanent Link |
Rolf Frei eicom GmbH | 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.
|
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |