{"id":209,"date":"2012-12-24T17:48:03","date_gmt":"2012-12-24T16:48:03","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=209"},"modified":"2023-11-08T19:59:58","modified_gmt":"2023-11-08T18:59:58","slug":"joining-temporal-intervals","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/","title":{"rendered":"Joining Temporal Intervals"},"content":{"rendered":"\n

From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a time point becomes just an additional filter criterion per temporal table (e.g. t1.some_date between t2.valid_from and t2.valid_to). But in some cases, this approach is not feasible, e.g. if you have to provide all relevant time intervals. I’ll explain in this post a solution approach based on the following data model.<\/p>\n\n\n\n

\"TemporalJoinModel\"<\/a><\/figure>\n\n\n\n

This model is based on the famous EMP and DEPT tables. I added a reference table for job just to get an additional table to join. You find the SQL script to create and populate the model here<\/a>.<\/p>\n\n\n\n

I’d like to query data from the tables EMPV, DEPTV, JOBV and EMPV (manager). Here is the content of the tables reduced to the data relevant for empno 7788 (SCOTT).<\/p>\n\n\n\n

Data Excerpt<\/span><\/path><\/path><\/svg><\/span>
SQL<\/span>> <\/span>SELECT<\/span> * <\/span>FROM<\/span> empv <\/span>WHERE<\/span> empno = <\/span>7788<\/span> <\/span>ORDER BY<\/span> valid_from;<\/span><\/span>\n<\/span>\nEMPVID EMPNO ENAME JOBNO  MGR HIREDATE     SAL COMM DEPTNO VALID_FROM  VALID_TO<\/span><\/span>\n------ ----- ----- ----- ---- ----------- ---- ---- ------ ----------- -----------<\/span><\/span>\n     <\/span>8<\/span>  <\/span>7788<\/span> SCOTT     <\/span>5<\/span> <\/span>7566<\/span> <\/span>19<\/span>-APR-<\/span>1987<\/span> <\/span>3000<\/span>          <\/span>20<\/span> <\/span>19<\/span>-APR-<\/span>1987<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>1989<\/span><\/span>\n    <\/span>22<\/span>  <\/span>7788<\/span> Scott     <\/span>5<\/span> <\/span>7566<\/span> <\/span>19<\/span>-APR-<\/span>1987<\/span> <\/span>3000<\/span>          <\/span>20<\/span> <\/span>01<\/span>-JAN-<\/span>1990<\/span> <\/span>31<\/span>-MAR-<\/span>1991<\/span><\/span>\n    <\/span>36<\/span>  <\/span>7788<\/span> Scott     <\/span>5<\/span> <\/span>7566<\/span> <\/span>19<\/span>-APR-<\/span>1987<\/span> <\/span>3300<\/span>          <\/span>20<\/span> <\/span>01<\/span>-APR-<\/span>1991<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>9999<\/span><\/span>\n<\/span>\nSQL<\/span>> <\/span>SELECT<\/span> * <\/span>FROM<\/span> jobv <\/span>WHERE<\/span> jobno = <\/span>5<\/span> <\/span>ORDER BY<\/span> valid_from;<\/span><\/span>\n<\/span>\nJOBVID JOBNO JOB     VALID_FROM  VALID_TO<\/span><\/span>\n------ ----- ------- ----------- -----------<\/span><\/span>\n     <\/span>5<\/span>     <\/span>5<\/span> ANALYST <\/span>01<\/span>-JAN-<\/span>1980<\/span> <\/span>20<\/span>-JAN-<\/span>1990<\/span><\/span>\n    <\/span>10<\/span>     <\/span>5<\/span> Analyst <\/span>21<\/span>-JAN-<\/span>1990<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>9999<\/span><\/span>\n<\/span>\nSQL<\/span>> <\/span>SELECT<\/span> * <\/span>FROM<\/span> deptv <\/span>WHERE<\/span> deptno = <\/span>20<\/span> <\/span>ORDER BY<\/span> valid_from;<\/span><\/span>\n<\/span>\nDEPTVID DEPTNO DNAME    LOC    VALID_FROM  VALID_TO<\/span><\/span>\n------- ------ -------- ------ ----------- -----------<\/span><\/span>\n      <\/span>2<\/span>     <\/span>20<\/span> RESEARCH DALLAS <\/span>01<\/span>-JAN-<\/span>1980<\/span> <\/span>28<\/span>-FEB-<\/span>1990<\/span><\/span>\n      <\/span>6<\/span>     <\/span>20<\/span> Research DALLAS <\/span>01<\/span>-MAR-<\/span>1990<\/span> <\/span>31<\/span>-MAR-<\/span>1990<\/span><\/span>\n     <\/span>10<\/span>     <\/span>20<\/span> Research Dallas <\/span>01<\/span>-APR-<\/span>1990<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>9999<\/span><\/span>\n<\/span>\nSQL<\/span>> <\/span>SELECT<\/span> * <\/span>FROM<\/span> empv <\/span>WHERE<\/span> empno = <\/span>7566<\/span> <\/span>ORDER BY<\/span> valid_from;<\/span><\/span>\n<\/span>\nEMPVID EMPNO ENAME JOBNO  MGR HIREDATE       SAL COMM DEPTNO VALID_FROM  VALID_TO<\/span><\/span>\n------ ----- ----- ----- ---- ----------- ------ ---- ------ ----------- -----------<\/span><\/span>\n     <\/span>4<\/span>  <\/span>7566<\/span> JONES     <\/span>4<\/span> <\/span>7839<\/span> <\/span>02<\/span>-APR-<\/span>1981<\/span>   <\/span>2975<\/span>          <\/span>20<\/span> <\/span>02<\/span>-APR-<\/span>1981<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>1989<\/span><\/span>\n    <\/span>18<\/span>  <\/span>7566<\/span> Jones     <\/span>4<\/span> <\/span>7839<\/span> <\/span>02<\/span>-APR-<\/span>1981<\/span>   <\/span>2975<\/span>          <\/span>20<\/span> <\/span>01<\/span>-JAN-<\/span>1990<\/span> <\/span>31<\/span>-MAR-<\/span>1991<\/span><\/span>\n    <\/span>32<\/span>  <\/span>7566<\/span> Jones     <\/span>4<\/span> <\/span>7839<\/span> <\/span>02<\/span>-APR-<\/span>1981<\/span> <\/span>3272<\/span>.<\/span>5<\/span>          <\/span>20<\/span> <\/span>01<\/span>-APR-<\/span>1991<\/span> <\/span>31<\/span>-<\/span>DEC<\/span>-<\/span>9999<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The following figure visualizes the expected result of a temporal join using the data queried previously.<\/p>\n\n\n\n
\"TemporalJoinOverview\"<\/a><\/figure>\n\n\n\n
In this case, six result records (intervals) are expected. As you see the result is dependent on the number of different intervals or the distinct VALID_FROM values. The driving object is valid from 19-APR-1987 until 31-DEC-9999. VALID_FROM values outside of the validity are irrelevant (e.g. 01-JAN-1980 and 02-APR-1981).<\/p>\n\n\n\n
Based on this information we can write the query. The highlighted inline view g produces a list of all distinct VALID_FROM values which will be used as additional join criterion for all temporal tables.<\/p>\n\n\n\n
Temporal Query (including semantic, no gaps)<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> e.empno,<\/span><\/span>\n       <\/span>MIN<\/span>(g.valid_from) <\/span>AS<\/span> valid_from,<\/span><\/span>\n       <\/span>LEAD<\/span>(<\/span>MIN<\/span>(g.valid_from) - <\/span>1<\/span>, <\/span>1<\/span>, <\/span>DATE<\/span> <\/span>'9999-12-31'<\/span>) <\/span>OVER<\/span>(<\/span><\/span>\n          <\/span>PARTITION<\/span> <\/span>BY<\/span> e.empno <\/span>ORDER BY<\/span> <\/span>MIN<\/span>(g.valid_from)<\/span><\/span>\n       ) <\/span>AS<\/span> valid_to,<\/span><\/span>\n       e.ename,<\/span><\/span>\n       j.job,<\/span><\/span>\n       e.mgr,<\/span><\/span>\n       m.ename <\/span>AS<\/span> mgr_ename,<\/span><\/span>\n       e.hiredate,<\/span><\/span>\n       e.sal,<\/span><\/span>\n       e.comm,<\/span><\/span>\n       e.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       d.loc<\/span><\/span>\n  <\/span>FROM<\/span> empv e<\/span><\/span>\n <\/span>INNER JOIN<\/span> (<\/span>SELECT<\/span> valid_from <\/span>FROM<\/span> empv<\/span><\/span>\n             <\/span>UNION<\/span><\/span>\n             <\/span>SELECT<\/span> valid_from <\/span>FROM<\/span> deptv<\/span><\/span>\n             <\/span>UNION<\/span><\/span>\n             <\/span>SELECT<\/span> valid_from <\/span>FROM<\/span> jobv) g<\/span><\/span>\n    <\/span>ON<\/span> g.valid_from <\/span>BETWEEN<\/span> e.valid_from <\/span>AND<\/span> e.valid_to<\/span><\/span>\n <\/span>INNER JOIN<\/span> deptv d<\/span><\/span>\n    <\/span>ON<\/span> d.deptno = e.deptno<\/span><\/span>\n       <\/span>AND<\/span> g.valid_from <\/span>BETWEEN<\/span> d.valid_from <\/span>AND<\/span> d.valid_to<\/span><\/span>\n <\/span>INNER JOIN<\/span> jobv j<\/span><\/span>\n    <\/span>ON<\/span> j.jobno = e.jobno<\/span><\/span>\n       <\/span>AND<\/span> g.valid_from <\/span>BETWEEN<\/span> j.valid_from <\/span>AND<\/span> j.valid_to<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> empv m<\/span><\/span>\n    <\/span>ON<\/span> m.empno = e.mgr<\/span><\/span>\n       <\/span>AND<\/span> g.valid_from <\/span>BETWEEN<\/span> m.valid_from <\/span>AND<\/span> m.valid_to<\/span><\/span>\n <\/span>WHERE<\/span> e.empno = <\/span>7788<\/span><\/span>\n <\/span>GROUP BY<\/span> e.empno,<\/span><\/span>\n          e.ename,<\/span><\/span>\n          j.job,<\/span><\/span>\n          e.mgr,<\/span><\/span>\n          m.ename,<\/span><\/span>\n          e.hiredate,<\/span><\/span>\n          e.sal,<\/span><\/span>\n          e.comm,<\/span><\/span>\n          e.deptno,<\/span><\/span>\n          d.dname,<\/span><\/span>\n          d.loc<\/span><\/span>\n <\/span>ORDER BY<\/span> empno, valid_from;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Query Result<\/span><\/path><\/path><\/svg><\/span>
EMPNO VALID_FROM  VALID_TO    ENAME JOB      MGR MGR_ENAME HIREDATE     SAL COMM DEPTNO DNAME    LOC<\/span><\/span>\n----- ----------- ----------- ----- ------- ---- --------- ----------- ---- ---- ------ -------- ------<\/span><\/span>\n 7788 19-APR-1987 31-DEC-1989 SCOTT ANALYST 7566 JONES     19-APR-1987 3000          20 RESEARCH DALLAS<\/span><\/span>\n 7788 01-JAN-1990 20-JAN-1990 Scott ANALYST 7566 Jones     19-APR-1987 3000          20 RESEARCH DALLAS<\/span><\/span>\n 7788 21-JAN-1990 28-FEB-1990 Scott Analyst 7566 Jones     19-APR-1987 3000          20 RESEARCH DALLAS<\/span><\/span>\n 7788 01-MAR-1990 31-MAR-1990 Scott Analyst 7566 Jones     19-APR-1987 3000          20 Research DALLAS<\/span><\/span>\n 7788 01-APR-1990 31-MAR-1991 Scott Analyst 7566 Jones     19-APR-1987 3000          20 Research Dallas<\/span><\/span>\n 7788 01-APR-1991 31-DEC-9999 Scott Analyst 7566 Jones     19-APR-1987 3300          20 Research Dallas<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The beauty of this approach is that it works with any granularity and it automatically merges identical intervals. In this example, I use a granularity of a day, but this approach works also for the granularity of seconds or even a fraction of a second, e.g. if you are using a TIMESTAMP data type to define the interval boundaries.<\/p>\n\n\n\n
Updated on 2012-12-28, emphasized the <\/em>possibility of wrong results and added a link to part 2<\/a> of this post.
<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":762,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[42,43],"class_list":["post-209","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-temporal-database","tag-valid-time"],"yoast_head":"\nJoining Temporal Intervals - Philipp Salvisberg's Blog<\/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:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Joining Temporal Intervals - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-24T16:48:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T18:59:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"746\" \/>\n\t<meta property=\"og:image:height\" content=\"408\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Joining Temporal Intervals\",\"datePublished\":\"2012-12-24T16:48:03+00:00\",\"dateModified\":\"2023-11-08T18:59:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/\"},\"wordCount\":489,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/TemporalJoinOverview1.png\",\"keywords\":[\"Temporal Database\",\"Valid Time\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/\",\"name\":\"Joining Temporal Intervals - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/TemporalJoinOverview1.png\",\"datePublished\":\"2012-12-24T16:48:03+00:00\",\"dateModified\":\"2023-11-08T18:59:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/TemporalJoinOverview1.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/TemporalJoinOverview1.png\",\"width\":746,\"height\":408},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/24\\\/joining-temporal-intervals\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Joining Temporal Intervals\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Joining Temporal Intervals - Philipp Salvisberg's Blog","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:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/","og_locale":"en_US","og_type":"article","og_title":"Joining Temporal Intervals - Philipp Salvisberg's Blog","og_description":"From time to time a customer asks me how to join multiple tables with temporal intervals (e.g. defined by two columns such as valid_from and valid_to per row). The solution is quite simple if you may limit your query to a certain point in time like now, yesterday or similar. Such a [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2012-12-24T16:48:03+00:00","article_modified_time":"2023-11-08T18:59:58+00:00","og_image":[{"width":746,"height":408,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Joining Temporal Intervals","datePublished":"2012-12-24T16:48:03+00:00","dateModified":"2023-11-08T18:59:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/"},"wordCount":489,"commentCount":1,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png","keywords":["Temporal Database","Valid Time"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/","url":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/","name":"Joining Temporal Intervals - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png","datePublished":"2012-12-24T16:48:03+00:00","dateModified":"2023-11-08T18:59:58+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/TemporalJoinOverview1.png","width":746,"height":408},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/24\/joining-temporal-intervals\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Joining Temporal Intervals"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/209","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=209"}],"version-history":[{"count":11,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions"}],"predecessor-version":[{"id":12744,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions\/12744"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/762"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
It’s important to notice that I’ve used an including semantic for VALID_TO in this example. If you use an excluding semantic (VALID_TO = VALID_FROM of the subsequent interval) you have to amend the calculation of the VALID_TO and the join criteria (BETWEEN is not feasible with excluding semantic). Furthermore, this example does not cover gaps in the historization. If you have gaps you need to amend the calculation of the VALID_TO column and ensure that you do not merge gaps. Merging intervals with a simple group by will produce wrong results if “disconnected” intervals have the same content. Issues are addressed in part 2<\/a> of this post.<\/p>\n\n\n\n