How to calculate diff in time of two diff time stamp

50 pts.
Tags:
AS/400
AS/400 TimeStamp
I have a table in that two fields stores the value of time stamp for eg 2009-12-10-20.00.26.424000 - DQ1CREATED 2009-12-10-20.00.26.860000-DQ2CREATED these two fields should be compared such that if there is a diff of 5 mins I need to get an alert as well as if any one of the field is blank I need to get an alert. This I need to implement in a job. Any suitable query for this? Thanks a lot in advance.

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 Retrieve Duration using XCL 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 SNDDTAQXCL (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).

Bruce Vining
(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:

SELECT * FROM CL_SCHED
WHERE HOUR(ENDING - STARTING) = 0 AND
MINUTE(ENDING - STARTING) < 50

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:

SELECT TIMESTAMPDIFF( 4,
CAST( CURRENT_TIMESTAMP - CAST(BIRTHDATE AS TIMESTAMP) AS CHAR(22) )
) AS AGE_IN_MINUTES FROM EMPLOYEE

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

Test first.

Tom

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • PrashanthR07
    thanks a lot for ur reply ...
    50 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following