| Author |
Message
|
| goldym |
 Posted: Tue Dec 06, 2005 11:05 am Post subject: Date Difference |
 |
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
Is there a function (MB5.0)to get a date difference between two dates to see if it exceeds 24 hours.
I have to Backout a message if there is a database connection error for up to 24 hours. I am storing the orignal InputRoot.MQMD.PutTime and InputRoot.MQMD.PutDate from the MQMD in the JMS header when BackOut = 0. Is there anyway to get the difference between those dates and the CURRENT_DATE and CURRENT_TIME when BackOut >0? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Tue Dec 06, 2005 11:06 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You want an INTERVAL.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| goldym |
| Posted: Tue Dec 06, 2005 8:33 pm Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
[quote="jefflowrey"]You want an INTERVAL.[/quote]
Thanks for the Tip Jeff that put me on the right track however when I use this code?
SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) HOUR TO MINUTE;
SET dateDiff = (CURRENT_DATE - InputRoot.MQMD.PutDate) DAY;
IF dateDiff > 1 AND
timeDiff > 1 THEN
SET exceeds = TRUE;
END If;
This returns returns an exception "invalid comparison" is this because the values are intervals? Is there anyway to compare the intervals to see if they have exceeded 24 hours. |
|
| Back to top |
|
|
| shrek |
| Posted: Wed Dec 07, 2005 5:01 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
| Since your datediff/timediff are in INTERVAL, you should compare them against the INTERVAL values only. |
|
| Back to top |
|
|
| wooda |
| Posted: Wed Dec 07, 2005 5:22 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
A literal INTERVAL would look like..
INTERVAL '1:40' HOUR TO MINUTE
Also since I'm nice I'll tell you that even if you got the syntax correct the logic of your check is flawed
What if the difference in date is 1 day and the difference in time is 1 hour ? (which is > 24 hours total)
or
What if the difference in date is 2 days and the difference in time is -1 hour ? (also >24 hours total) |
|
| Back to top |
|
|
| goldym |
| Posted: Wed Dec 07, 2005 7:16 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
Thanks for the responses and thanks for being nice 
Yes i figured out my logic was incorrect when i ran my test for this code
SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) HOUR TO MINUTE;
I got the difference INTERVAL '4:01'. What I can't figure out how is how to get my 4:01 in total number of seconds to see if its greater than 86400. Which may be simple but i can't figure it out I have a brain block right now
IF timeDiff > 60*60*24 THEN
SET exceeds = 'TRUE';
ELSE
SET exceeds = 'FALSE';
END IF; |
|
| Back to top |
|
|
| wooda |
| Posted: Wed Dec 07, 2005 7:31 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
All you need to do is use compatible types
timeDiff is an INTERVAL so the other half of your equation needs to be an INTERVAL too.
eg.
| Code: |
IF timeDiff > INTERVAL '24:00' HOUR TO MINUTE THEN
... |
|
|
| Back to top |
|
|
| goldym |
| Posted: Wed Dec 07, 2005 7:43 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
[quote="wooda"]All you need to do is use compatible types
timeDiff is an INTERVAL so the other half of your equation needs to be an INTERVAL too.
eg.
[code]IF timeDiff > INTERVAL '24:00' HOUR TO MINUTE THEN
...[/code][/quote]
Thanks Wooda that worked perfectly. I used seconds instead of hours to minutes. Do you still think my logic is flawed?
SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) SECOND;
IF timeDiff > INTERVAL '86400.00' SECOND THEN
SET exceeds = 'TRUE';
ELSE
SET exceeds = 'FALSE';
END IF;
RETURN TRUE; |
|
| Back to top |
|
|
| wooda |
| Posted: Wed Dec 07, 2005 8:59 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
| Quote: |
SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) SECOND;
IF timeDiff > INTERVAL '86400.00' SECOND THEN
SET exceeds = 'TRUE';
ELSE
SET exceeds = 'FALSE';
END IF; |
In your first post you also had a put date.
So comparing times without considering the date could means that you are not really considering the time elasped between the date+time in the message and the current time.
What if the message was put yesterday ? |
|
| Back to top |
|
|
| goldym |
| Posted: Wed Dec 07, 2005 11:20 am Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
[quote="wooda"][quote]SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) SECOND;
IF timeDiff > INTERVAL '86400.00' SECOND THEN
SET exceeds = 'TRUE';
ELSE
SET exceeds = 'FALSE';
END IF; [/quote]
In your first post you also had a put date.
So comparing times without considering the date could means that you are not really considering the time elasped between the date+time in the message and the current time.
Yes you are correct. I updated the logic
SET dateDiff = (CURRENT_DATE - InputRoot.MQMD.PutDate) DAY;
SET timeDiff = (CURRENT_GMTTIME - InputRoot.MQMD.PutTime) SECOND;
IF dateDiff > INTERVAL '1' DAY THEN
SET Environment.Variables.Exceeds = 'True';
END IF;
IF dateDiff < INTERVAL '1' DAY THEN
IF timeDiff > INTERVAL '86400.00' SECOND THEN
SET Environment.Variables.Exceeds = 'True';
ELSE IF timeDiff < INTERVAL '86400.00' SECOND THEN
SET Environment.Variables.Exceeds = 'False';
END IF;
END IF;
END IF;
IF dateDiff = INTERVAL '1' DAY THEN
IF timeDiff < INTERVAL '86400.00' SECOND THEN
SET Environment.Variables.Exceeds = 'False';
ELSE IF timeDiff > INTERVAL '86400.00' SECOND THEN
SET Environment.Variables.Exceeds = 'True';
END IF;
END IF;
END IF; |
|
| Back to top |
|
|
| goldym |
| Posted: Wed Dec 07, 2005 10:24 pm Post subject: |
|
|
Centurion
Joined: 24 Jun 2005
Posts: 116
|
Question
Is it possible to CAST InputRoot.MQMD.PutTime CURRENT_GMTTIME to an INTERVAL I checked the help and in section. It said that I could but when I tried it didn't work. My logic is not working can anyone help. I am just trying to get the difference so I can see if the time exceeds 24 hours. I change the putTime to be slightly over 24 hours. When I run the code below
DECLARE timeDiff INTERVAL;
DECLARE dateDiff INTERVAL;
DECLARE totalsecs INTERVAL;
DECLARE yesterday INTERVAL;
DECLARE SecondsInDay INTERVAL;
SET SecondsInDay = INTERVAL '846000' SECOND;
SET dateDiff = (CURRENT_DATE - InputRoot.MQMD.PutDate) DAY;
SET Environment.Variables.Exceeds = 'False';
IF dateDiff > INTERVAL '1' DAY THEN
SET Environment.Variables.Exceeds = 'True';
END IF;
IF dateDiff = INTERVAL '1' DAY THEN
SET yesterday = (SecondsInDay - InputRoot.PutTime);-->fails here because of the different formats
SET totalsecs = (yesterday + CURRENT_GMTTIME );
IF totalsecs > SecondsInDay THEN
SET Environment.Variables.Exceeds = 'True';
END IF; |
|
| Back to top |
|
|
| wooda |
| Posted: Thu Dec 08, 2005 1:25 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
Come on this is not that complicated. Apply some common sense.
You clearly cannot cast a time as an interval. Think about it.
An interval is by defintion the difference between two time points.
And you clearly cannot subtract a time from an interval.
SecondsInDay - InputRoot.PutTime is INTERVAL - TIME
try doing that yourself.
eg.
4hours minus 12:32pm
what did you get 
The other way around makes sense but not that way around.  |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Dec 08, 2005 8:26 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi, here is some step by step code that will do most of this for you. You just need to check the number of seconds you get back. Basically it converts putdate and puttime to a timestamp and subtracts this from currenttimestamp to give an interval in seconds.
Note the code is written to work on V6 and V5 of message broker. However due to the enhanced CAST support in V6, the V6 code is one line, but the V5 code is quite a few more
| Code: |
CREATE SCHEMA test
CREATE COMPUTE MODULE Dummy_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
/*set up output msg*/
SET OutputRoot.MQMD = InputRoot.MQMD;
/*output data to validate answer*/
SET OutputRoot.XML.Top.DataTest.StartingTest = 'BEGIN';
SET OutputRoot.XML.Top.DataTest.PutDate = OutputRoot.MQMD.PutDate;
SET OutputRoot.XML.Top.DataTest.PutTime = OutputRoot.MQMD.PutTime;
SET OutputRoot.XML.Top.DataTest.CurrentDate = Current_DATE;
SET OutputRoot.XML.Top.DataTest.CurrentTime = CURRENT_GMTTIME;
SET OutputRoot.XML.Top.DataTest.CurrentTimeStamp = CURRENT_GMTTIMESTAMP;
/* V6 version - one single line of code*/
/* this line works in V6 and saves a lot of code*/
/*SET OutputRoot.XML.Top.DataTest.ResultV6 = (CURRENT_GMTTIMESTAMP - CAST( OutputRoot.MQMD.PutDate, OutputRoot.MQMD.PutTime AS GMTTIMESTAMP) ) SECOND;*/
/* V5 Version - a little extra work required due to lack of enhanced CAST code*/
/*declare and initialise local variables to make a timestamp out of put date and put time*/
DECLARE putDate CHARACTER CAST( OutputRoot.MQMD.PutDate AS CHARACTER);
SET putDate = v5ExtractValueFromDateOrTime(putDate);
SET OutputRoot.XML.Top.DataTest.putDateExtract = putDate ;
DECLARE putTime CHARACTER CAST( OutputRoot.MQMD.PutTime AS CHARACTER);
SET putTime = v5ExtractValueFromDateOrTime(putTime);
SET OutputRoot.XML.Top.DataTest.putTimeExtract = putTime ;
DECLARE putTimeStamp TIMESTAMP CAST( (putDate || ' ' || putTime) AS GMTTIMESTAMP);
SET OutputRoot.XML.Top.DataTest.ResultV5 = (CURRENT_GMTTIMESTAMP - putTimeStamp ) SECOND;
END;
CREATE FUNCTION v5ExtractValueFromDateOrTime( IN source CHARACTER ) RETURNS CHARACTER
BEGIN
/*find the begin and end single quotes, and create a substring of the data between them*/
DECLARE begin INTEGER POSITION( '''' IN source FROM 0);
SET begin = begin+1;
DECLARE end INTEGER POSITION( '''' IN source FROM begin );
/*extract the date or time value*/
RETURN SUBSTRING(source FROM begin FOR end-begin);
END;
END MODULE; |
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Dec 08, 2005 8:54 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is it faster to assign in the declare, rather than in a set?
I would tend to write
| Code: |
DECLARE begin INTEGER;
SET begin = POSITION( '''' IN source FROM 0) +1; |
rather than what you have. Is this just a style thing, or a speed trick?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Dec 08, 2005 9:31 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Interesting question.
In general you will have a slight speed gain by initialising on the same line as the DECLARE as you will not have to perform the SET statement, but the difference is small. You also ensure that your variables are never NULL if you do this (unless you initialise to NULL)
However, in V6 the following sort of cases can be misleading:
| Code: |
| DECLARE test DATE DATE '2005-05-05'; |
This will be quicker than a DECLARE followed by a SET
however:
| Code: |
| DECLARE test2 DATE '2005-05-06'; |
will be slower than the first example above as the literal string '2005-05-06' is an implicit CAST to a DATE from a CHAR literal, which is slower than initialising from a date literal in the first example. However the DATE DATE syntax does look confusing, and the implicit CAST looks better, even if it is slower.
You just have to think carefully about what you are doing
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
