{"id":133,"date":"2010-08-11T10:08:23","date_gmt":"2010-08-11T14:08:23","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2010\/08\/11\/calculating-the-difference-between-two-dates-or-times-in-db2\/"},"modified":"2010-08-11T10:08:23","modified_gmt":"2010-08-11T14:08:23","slug":"calculating-the-difference-between-two-dates-or-times-in-db2","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2010\/08\/11\/calculating-the-difference-between-two-dates-or-times-in-db2\/","title":{"rendered":"Calculating the difference between two dates or times in DB2"},"content":{"rendered":"<p>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.<\/p>\n<ul>\n<li>1 : Fractions of a second<\/li>\n<li>2 : Seconds<\/li>\n<li>4 : Minutes<\/li>\n<li>8 : Hours<\/li>\n<li>16 : Days<\/li>\n<li>32 : Weeks<\/li>\n<li>64 : Months<\/li>\n<li>128 : Quarters of a year<\/li>\n<li>256 : Years<\/li>\n<\/ul>\n<p>The second parameter should contain the subtraction formula between two timestamps, with the results converted to character format.  Below is one example of usage.<\/p>\n<pre class=\"code\">\nselect \ntimestampdiff(\n  16, \n  char(timestamp('2010-01-11-15.01.33.453312') - current timestamp))  \nfrom sysibm.sysdummy1;\n<\/pre>\n<p>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 &#8212; 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This note demonstrates how to calculate the difference between two dates or two times, ie. timestamps, in a DB2 SQL inline function.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-db2"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":1,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":366,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions\/366"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}