The following tutorial explains about how to find the difference of two timestamps and also covers how to calculate the difference of two dates . In many situation , you may need to calculate the difference between two timestamps. For example , your application , may capture the access date and time of a user as timestamp in a user table. User account may be locked when a user makes 3 continues unsuccessful login attempts . The access date and time of the userid is stored. After 72 hours , we may need to unlock the locked accounts using query. In this situation , you have to calculate the difference between current timestamp and timestamp when the user account is locked. Another example , in company , IN_TIME and OUT_TIME may be captured as timestamps. Now you can calculate the duty hours by subtracting the OUT_TIME timestamp with IN_TIME timestamp. And date and timestamp difference calculation may also be required for age calculation , service left for retiredment , etc.
1. Calculate difference between two timestamps :
Difference between two timestamps can be calculated in the following ways
1. timestamp(exp) - timestamp(exp1) OR timestamp(exp) -exp1 OR exp-timestamp(exp1) , where exp, exp1 are timestamp or valid string representation of timestamp. The result of subtracting one timestamp from another will be timestamp duration which is a decimal(20,6) number that represents the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps . The result is in the format of YYYYMMDDHHMMSS.ZZZZZZ
2. date(exp) - date(exp1) where exp, exp1 are timestamp or valid string representation of timestamp. The result will be a decimal duration which is decimal(8,0) number represents the difference between two timestamp values as YYYYMMDD. Both exp1 & exp2 are casted to date
First one is more preferred than second way . IInd one used , when you calculate difference of timestamps , if don't want account the time element of timestamps.
examples :
values timestamp('2012-01-05-12.00.00')-timestamp('2011-02-01-12.00.00') , returns 1104000000.000000 that means 11 months, 4 days
values date('2012-01-01-12.00.00')-date('2011-01-01-12.00.00') , returns 10000 , that means 1 year
values timestamp('2012-01-05-12.00.00')-timestamp_iso('2011-02-01') , returns 1104120000.000000 which is 11 months , 4 days , 12 hours
values current timestamp-timestamp(lastaccess)
To calculate the above results , in days , weeks, months , etc , db2 provides a function calledtimestampdiff()
Syntax : timestampdiff (n, char( timestamp(exp)- timestamp(exp1)))
where n can be 1,2,4,8,16,32,64,128 and 256 . 1 = Fractions of a second , 2 = Seconds , 4 = Minutes , 8 = Hours , 16 = Days , 32 = Weeks , 64 = Months , 128 = Quarters , 256 = Years ,
eg. values timestampdiff(16, char(timestamp('2012-01-05-12.00.00')-timestamp('2011-02-01-12.00.00'))) , returns 334 , which means 11 months and 4 days.
update user set login_failed_attempts=0 where timestampdiff(8,char(current timestamp-timestamp_iso(lastaccess))) >=72
select timestampdiff(8, char(timestamp(OUT_TIME)-timestamp(IN_TIME))) from employee , returns the duty hours
Note : The value returned by the above functions is an approximate value , because it does not account for leap years and assumes only 30 days per month.
1. Calculate difference between two Dates :
difference between two dates can be calculated using
date(exp) - date(exp1) OR date(exp) -exp1 OR exp-date(exp1) , where exp, exp1 are date or valid string representation of date. The result will be a decimal duration which is decimal(8,0) number represents the difference between two date values as YYYYMMDD.
Examples :
values date('2012-02-01') - date('2012-01-01') , returns 100 which is 1 month and zero days
values '2012-01-01' - date('2012-02-01')
values current date-DATE('1976-08-21') , returns 350515 which is 35 years , 5 months and 15 days.
select current date - date(doj) from employee , returns the duration of service in the organization
You can use timestampdiff() to calculate difference between two dates . Convert date to timestamp then calculate the difference using the function
eg. select timestampdiff(256,char(timestamp_iso(current date) -timestamp_iso (dob))) from employee , returns the number of years between current date and birthdate
0 comments:
Post a Comment