Dynamically Generating Dates in Oracle to Simulate a Warehouse Dates Dimension

By Lynn Walton – Sr. Software Engineer – ADP Cobalt SEO Team


Working for the SEO team in the automotive industry we track quite a bit of trending metrics.   We had a challenge in which the data we want to retrieve was not stored in a typical data warehouse where you have access to a dimensional model that may be more easily queried for specific grains (time periods). Working in a typical relational database introduces some difficulties when trying to query historical data.

Querying this data with typical SQL

I needed to query for data aggregated by month for a 14 month history.  The data to be aggregated was such that there could be months where no data existed. Since we wanted those missing months represented,  I found myself wishing I could left join to the equivalent of a dates dimension in a data warehouse.  Since the oracle data source I was using didn’t have such a table I ran across a way to do what I wanted without having to resort to the obvious but ugly union which would require passing all 14 month dates as parameters:

select :fourteenMonthAgoDate from dual
select :thirteenMonthAgoDate  from dual
union  ….

Improving the readability using built in Oracle statements

Taking advantage of Oracle’s Hierarchical functions allows us to do it in a much more compact way (though proprietary to Oracle) that only requires passing in at most two parameters for start and end dates.  If what you’re after includes the most current time period then sysdate can be used in place of the end date parameter.

SELECT :startDate + LEVEL - 1 AS the_date  FROM dual
CONNECT BY LEVEL <= TRUNC(sysdate) - TRUNC(:startDate) + 1;

So if run on Nov 19, 2013, the above will yield something like this assuming a value for :startDate that is the same as sysdate -5:

11/14/2013 1:40:04 PM
11/15/2013 1:40:04 PM
11/16/2013 1:40:04 PM
11/17/2013 1:40:04 PM
11/18/2013 1:40:04 PM
11/19/2013 1:40:04 PM

In my particular case I was after the first of the month dates for the most recent 14 months so I came up with the following:

SELECT the_date as fom_date FROM (
SELECT :startDate + LEVEL - 1 AS the_date
FROM dual
CONNECT BY LEVEL <= TRUNC(sysdate) - TRUNC(:startDate) + 1
) WHERE TO_CHAR (the_date, 'DD') = '01' ;

On Nov 19th, with :startDate equal to a date representing ‘10/1/2012 12:00:00 AM’  this yields :

10/1/2012 12:00:00 AM
11/1/2012 12:00:00 AM
12/1/2012 12:00:00 AM
1/1/2013 12:00:00 AM
. . .
9/1/2013 12:00:00 AM
10/1/2013 12:00:00 AM
11/1/2013 12:00:00 AM


Oracle has many built in statements and functions that can save quite a bit of time and improve the readability of the code.   Using this technique made the code very concise.   For more on  the CONNECT BY LEVEL technique visit http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques#CONNECT_BY_LEVEL

About collectivegenius
Everyone has a voice and great ideas come from anyone. At Cobalt, we call it the collective genius. When technical depth and passion meets market opportunity, the collective genius is bringing it’s best to the table and our customers win.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: