Skip to Content
Author's profile photo Former Member

Using Temporal Join to fetch the result set within the Time interval in SAP HANA

Hello All,

In this blog, i am going to explain how to use Temporal Join feature available in HANA to fetch the Records from transaction table based on Time interval fields of Master Table.


Sometime we come across scenario where in we need to analyse the data by joining Transaction table and Master table on key column and fetch the data for a particular time period as speicified in From and To dates of Master table. In this case we need to go for a Non Equi join. This can be easily achieved through Script based Views where in you use either BETWEEN operator or > and < operator. How do we achieve the same using Analytical view in HANA?


To achieve the above stated problem in Analytical view, we have to go for a Temporal join. You create a temporal join using the temporal column that specifies the time interval with the start and the end date. The result set is fetched based on the time interval mapped using the temporal column. That means a record is only included in the result set if the time interval lies within the valid time interval. This feature is available in SAP HANA starting from SP05.

I have created 2 tables Product_master and Product_sales table and loaded with sample data as shown below,

Product Master:




Now lets create an attribute view containing master table Product_master.


Also we will create Analytical view with Product_sales table in data foundation layer and lets try to join above created attribute view in Logical join node.


In the logical join, lets create a temporal join between the date field of the fact table to an interval (to and from) field of the attribute view. The supported data types are: timestamp, date, and integer.

Now select Temporal column that indicates time column in fact table, from and to column from attribute view which specifies time interval and Temporal condition on whether to include or exclude Dates. The various options available for Temporal condition are, Include to and Exclude from, Exclude both, Include to. We will keep Exclude to and Include from option.


Activate and run the Analytical view and you can see the output.


We can see that P001 product value for date 2014-03-10, P003 product value for date 2014-04-10 and 2014-06-28 has been filtered since they fall beyond the time interval. Also you can notice P002 product value for date 2014-04-26 has been filtered since we have kept Exclude to and Include from option.

Thus we are able to achieve the intended result through Analytical view.

Thanks for reading this blog, Your Suggestions and feedbacks are most welcome 🙂



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Petr Stastny
      Petr Stastny


      honestly I do not understand the example. The table content you have introduced I would call inconsistent database. How can I have a sales for product which is not valid for that date?


      Author's profile photo Former Member
      Former Member

      The blog is explanatory. The main purpose is to explain how Temporal Table and Temporal Joins work within SAP HANA. It succeeded to deliver this message 🙂

      Author's profile photo Bojan Dujic
      Bojan Dujic

      The example is, from an transactional perspective, not a real-life example :). I do however see how this temporal join is supposed to work.

      Author's profile photo Jaime Jimenez
      Jaime Jimenez



      Simple and well explained.


      Author's profile photo Former Member
      Former Member

      Good explanation Akshay ......keep post new documents too


      Author's profile photo Former Member
      Former Member

      Thanks Akshay, Nicely explained......

      Author's profile photo Eddie Ng
      Eddie Ng

      Nice document explaining temporal joins. i have a dilemma perhaps not related to temporal join. We are trying to achieve 'provide' joins in HANA graphical views (preferably). Anyone has any idea how to achieve this? We need to join few HR PA* tables to constrcut the employee master.

      any leads are deeply appreciated.


      Author's profile photo Niranjan Garud
      Niranjan Garud

      Very nicely explained.

      As per my understanding, Temporal join works in case of date range. What if I have to join on two range columns (From_Value and To_Value) which are not of type date? How can we achieve this in graphical view?