Business time with several joins (Temporal logic in db2)

Question

I am working with DB2 and its feature "Temporal logic" (if you are interesting in the topic - https://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/index.html gives an introduction). I got one question about an issue which I cannot understand so far:

This example will work perfectly fine:

SELECT * FROM policy 
FOR BUSINESS_TIME FROM '2009-01-01' TO '2011-01-01'

But if I want to join more tables, I get errors that the syntax is wrong. And there are no examples (read: I cannot find examples) of how to do it.

Yes, I know how to avoid this "feature", and make it work with sub select. And even between date_from and date_to won't help here. Because this BUSINESS_TIME is not equal date_to.

In my case, something like:

where'2009-01-01' <='2009-01-01'<'2011-01-01'

doesn't work.

Update in DB2 should be like this:

 where '2009-01-01'<='2009-01-01'
   and '2009-01-01'<'2011-01-01'

Thanks a lot in advance!

Answer 1

Joining also works - this is one of my examples joining two tables on BUSINESS TIME

SELECT u.name, u.BUSINESS_START, u.BUSINESS_END
     , d.name, d.BUSINESS_START, d.BUSINESS_END
     , max(u.BUSINESS_START, d.BUSINESS_START) as Result_BUSINESS_START
     , min(u.BUSINESS_END, d.BUSINESS_END) as Result_BUSINESS_END
  FROM Praesident_USA FOR business_time 
                      FROM '1970-01-01' TO CURRENT DATE U
 INNER JOIN KANZLER_D FOR business_time 
                      FROM '1970-01-01' TO CURRENT DATE D
    ON d.BUSINESS_START <= u.BUSINESS_END 
       AND d.BUSINESS_END >= u.BUSINESS_START 

I hope you find this helpful for your scenario.