View previous topic :: View next topic
|
Author |
Message |
saubhik
New User
Joined: 21 Sep 2007 Posts: 35 Location: kolkata
|
|
|
|
Hi All,
My requirement is to calculate the difference between the two db2 table timestamp is 48 hours or not.
We have to fields
1. current timestamp. char(26)
2. created timestamp.char(26)
I need to find out the difference between these 2 fields is greater than 48 hours or 2 days or not.
Please let me know how to calculate this. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
Quote: |
Please let me know how to calculate this. |
In Assembler or PL/I? |
|
Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007 Posts: 35 Location: kolkata
|
|
|
|
In PL1 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Do you know the format?
If not, then you are sunk.
If so, then can you calculate the number of hours, minutes, or seconds each is, and then maybe subtract, and then maybe compare? |
|
Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007 Posts: 35 Location: kolkata
|
|
|
|
I know the format of timestamp. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
since you are talking about 2 days,
simply extract the date portion,
populating a field that will work with the DAYS intrinsic function, see the example at bottom of page,
then add 2 days. reformat to your timestamp format and compare.
a wander thru the PL1 reference manual is all it takes,
unless of course all you want is someone to provide the code for you. |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
methinks we need more resolution - 48 hours is really the requirement |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Phrzby Phil wrote: |
methinks we need more resolution - 48 hours is really the requirement |
good chance. if so, he only needs to research the manual a little more. |
|
Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007 Posts: 35 Location: kolkata
|
|
|
|
yes.48 hours calculation is actual requirement. |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
We look forward to your solution, based on ample suggestions above. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Phrzby Phil wrote: |
methinks we need more resolution - 48 hours is really the requirement |
good one, had me going for a few minutes.
as in what is the difference between 48 hrs and 2 days? |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
Quote: |
as in what is the difference between 48 hrs and 2 days? |
yyyymm03235900 - yyyymm01000100 = 2 days or almost 72 hours...
yyyymm03000100 - yyyymm01235900 = 2 days or just over 24 hours... |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
I should not have smoked that thing! |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
dbzTHEdinosauer wrote: |
I should not have smoked that thing! |
Not so, it sometimes helps...grin....
FWIW, a quick subtraction of just the days to determine if the difference is equal to two before the more involved hour calculation might save significant time if the difference is usually less than one ore greater than three. |
|
Back to top |
|
|
saubhik
New User
Joined: 21 Sep 2007 Posts: 35 Location: kolkata
|
|
|
|
Solution is geiven below.Let me know if it is correct or not.
hours_difference = (days difference)*24 + hours difference + (minutes difference) / 60
=[days (current tmstmp) - days (last_timestamp))] *24 +
[Hour(current tmstmp) - Hour(last_timestamp)] +
[Minute(current tmsmp) - Minute(last_timestamp)]/60 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
We don't need to let you know - just test it on an appropriate set of values.
Some will be exactly 48 hours, some a second longer, some shorter, etc. etc. etc.
Even test with the timestamps in the wrong order - do you test for this and generate an error message?
Designing test data is part of a programmer's responsibility. |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Dick -
Don't worry about it. You get up 6-7 hours before I do, so mistakes will happen. |
|
Back to top |
|
|
|