As you indicate that an alert is needed when either of the two fields are blank that implies that one write operation within an application provides both fields. As such I would recommend a trigger over the table. The trigger program would validate that both fields are non-blank. If blank an alert is sent and the trigger returns. If non-blank the trigger determines the difference/duration between the two timestamps in minutes. If greater than 5 minutes an alert is sent. In either case the trigger now returns.
You do not indicate what mechanism you want to use for generating the alert or your language of choice. Assumming CL you have a variety of alert choices (message queues — break mode or otherwise, data queues, journal entries, etc). From CL the <a href=”http://www.powercl.com/xcl/xclcommands/rtvdurxcl”>Retrieve Duration using XCL</a> command, if you have eXtreme CL installed, can be used to determine the difference in minutes between the two timestamps. Likewise commands such as SNDMSG, SNDPGMMSG, SNDBRKMSG, SNDJRNE (all system provided commands) or <a href=”http://www.powercl.com/xcl/xclcommands/snddtaqxcl”>SNDDTAQXCL</a> (another eXtreme CL command) could be used when sending the alert.
Triggers would provide you with real-time alert capability external to the program(s) writing the record/row. Any “query” approach would be after the fact (unless imbedded within the file definition as with a trigger or similar approach).
(one of the eXtreme CL developers)
IN RPG, you can use SUBDUR or %DIFF to get the difference between the two values.
In terms of a ‘query’, the SQL Reference shows an example of selecting all “classes” from a class schedule table where the class duration is less than 50 minutes:<pre>
SELECT * FROM CL_SCHED
WHERE HOUR(ENDING – STARTING) = 0 AND
MINUTE(ENDING – STARTING) < 50</pre>The example may be useful in illustrating an issue where the ‘minutes’ part of a duration is important. A class starting at 2:00 PM and ending at 3:45 PM results in 1 hour plus 45 minutes.
A second example shows the TIMESTAMPDIFF() function:<pre>
SELECT TIMESTAMPDIFF( 4,
CAST( CURRENT_TIMESTAMP – CAST(BIRTHDATE AS TIMESTAMP) AS CHAR(22) )
) AS AGE_IN_MINUTES FROM EMPLOYEE</pre>This does a type (4) difference which is defined as ‘minutes’. It takes CURRENT_TIMESTAMP and finds the difference between it and the employee’s birthdate.
I suppose you could request WHERE TIMESTAMPDIFF( 4,DQ2CREATED-DQ1CREATED)>5.