{"id":7165,"date":"2022-09-07T06:28:29","date_gmt":"2022-09-07T00:58:29","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/"},"modified":"2022-09-07T06:28:29","modified_gmt":"2022-09-07T00:58:29","slug":"solved-calculate-time-diffrence-in-sql-with-shifts","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/","title":{"rendered":"[Solved] Calculate time diffrence in SQL with shifts"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Use a recursive sub-query factoring clause to generate each day within your time ranges and then correlate that with your shifts to restrict the time for each day to be within the shift hours and then aggregate to get the total:<\/p>\n

Oracle 18 Setup<\/strong>:<\/p>\n

CREATE TABLE times ( start_date, End_Date ) AS\nSELECT DATE '2017-02-21' + INTERVAL '07:52:32' HOUR TO SECOND,\n       DATE '2017-02-22' + INTERVAL '09:56:32' HOUR TO SECOND\nFROM   DUAL\nUNION ALL\nSELECT DATE '2017-02-21' + INTERVAL '09:52:32' HOUR TO SECOND,\n       DATE '2017-02-23' + INTERVAL '17:52:32' HOUR TO SECOND\nFROM   DUAL;\n\nCREATE TABLE weekly_shifts ( shift_day, Start_Time, End_Time ) AS\nSELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL\nSELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL\nSELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL\nSELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL\nSELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL;\n<\/code><\/pre>\n
Query 1<\/strong>:<\/p>\n
WITH days ( id, start_date, day_start, day_end, end_date ) AS (\n  SELECT ROWNUM,\n         start_date,\n         start_date,\n         LEAST( TRUNC( start_date ) + INTERVAL '1' DAY, end_date ),\n         end_date\n  FROM   times\nUNION ALL\n  SELECT id,\n         start_date,\n         day_end,\n         LEAST( day_end + INTERVAL '1' DAY, end_date ),\n         end_date\n  FROM   days\n  WHERE  day_end < end_date\n)\nSELECT start_date,\n       end_date,\n       SUM( shift_end - shift_start ) AS days_worked_on_shift\nFROM   (\n  SELECT ID,\n         start_date,\n         end_date,\n         GREATEST( day_start, TRUNC( day_start ) + start_time ) AS shift_start,\n         LEAST( day_end, TRUNC( day_start ) + end_time ) AS shift_end\n  FROM   days d\n         INNER JOIN\n         weekly_shifts w\n         ON ( TO_CHAR( d.day_start, 'DY' ) = w.shift_day )\n)\nGROUP BY id, start_date, end_date;\n<\/code><\/pre>\n
Result<\/strong>:<\/p>\n
START_DATE          END_DATE            DAYS_WORKED_ON_SHIFT\n------------------- ------------------- --------------------\n2017-02-21 07:52:32 2017-02-22 09:56:32 0.414259259259259259\n2017-02-21 09:52:32 2017-02-23 17:52:32 1.078148148148148148\n<\/code><\/pre>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            2<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved Calculate time diffrence in SQL with shifts <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Use a recursive sub-query factoring clause to generate each day within your time ranges and then correlate that with your shifts to restrict the time for each day to be within the shift hours and then aggregate to get the total: Oracle 18 Setup: CREATE TABLE times ( start_date, End_Date ) AS SELECT DATE … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[838,341],"class_list":["post-7165","post","type-post","status-publish","format-standard","hentry","category-solved","tag-oracle","tag-sql"],"yoast_head":"\n[Solved] Calculate time diffrence in SQL with shifts - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Calculate time diffrence in SQL with shifts - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Use a recursive sub-query factoring clause to generate each day within your time ranges and then correlate that with your shifts to restrict the time for each day to be within the shift hours and then aggregate to get the total: Oracle 18 Setup: CREATE TABLE times ( start_date, End_Date ) AS SELECT DATE ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-07T00:58:29+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Calculate time diffrence in SQL with shifts\",\"datePublished\":\"2022-09-07T00:58:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/\"},\"wordCount\":63,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"oracle\",\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/\",\"name\":\"[Solved] Calculate time diffrence in SQL with shifts - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-09-07T00:58:29+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-calculate-time-diffrence-in-sql-with-shifts\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Calculate time diffrence in SQL with shifts\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Calculate time diffrence in SQL with shifts - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Calculate time diffrence in SQL with shifts - JassWeb","og_description":"[ad_1] Use a recursive sub-query factoring clause to generate each day within your time ranges and then correlate that with your shifts to restrict the time for each day to be within the shift hours and then aggregate to get the total: Oracle 18 Setup: CREATE TABLE times ( start_date, End_Date ) AS SELECT DATE ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/","og_site_name":"JassWeb","article_published_time":"2022-09-07T00:58:29+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Calculate time diffrence in SQL with shifts","datePublished":"2022-09-07T00:58:29+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/"},"wordCount":63,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["oracle","sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/","url":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/","name":"[Solved] Calculate time diffrence in SQL with shifts - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-07T00:58:29+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-calculate-time-diffrence-in-sql-with-shifts\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Calculate time diffrence in SQL with shifts"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=7165"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7165\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=7165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=7165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=7165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}