To do so, we can utilize the timestampdiff() function provided by DB2. This function takes in two parameters. The first parameter is a numeric value indicating the unit in which you wish to receive the results in; the values must be one of the following.
- 1 : Fractions of a second
- 2 : Seconds
- 4 : Minutes
- 8 : Hours
- 16 : Days
- 32 : Weeks
- 64 : Months
- 128 : Quarters of a year
- 256 : Years
The second parameter should contain the subtraction formula between two timestamps, with the results converted to character format. Below is one example of usage.
select
timestampdiff(
16,
char(timestamp('2010-01-11-15.01.33.453312') - current timestamp))
from sysibm.sysdummy1;
The result from this statement is 210 at the time of the writing. Notice that even though the first timestamp is set to be prior than the current timestamp, the outcome is still positive — This function returns the absolute value (ie. always positive) reflecting the difference in time between two timestamps. Also, take note that the result will always be an integer, thus it can only be considered an estimation of the date/time difference rather than an exact one.
nice thanks for all ! go a head
Hi Thanks for this. Helped me do some timestamp differences. Just one thing, you say it is only considered an estimation. However I wanted to see the hours difference between 2 datetime stamps. I used a lower Value of minutes and then divide it by 60 and then cast it and get a more accurate value. For example:
I have 2 timestamps
2015-02-24 14:23:29.000000
And 2015-02-24 10:09:15.000000
If I use the timestampdiff with hours it just gives 4 hrs as the result
But if you use minutes and then divide that by 60 I get 4.23 hrs
Cast(timestampdiff(4, Char(timestamp(2015-02-24 14:23:29.000000 )-Timestamp(2015-02-24 10:09:15.000000)))as decimal(10,2))/60)
Thanks