Development

Creating a Calendar Table

0

A calendar table is frequently used in queries to provide gap filling for rows that don’t match inner table data. It can also provide extended information such as the week, quarter, fiscal flags, holidays, and business days. This post will walk through one way of creating a simple calendar table and some extended information.

The following method for generating a calendar table relies on already having a numbers table with n rows for the range you wish to populate. For example, a calendar table beginning January 1, 2000 and ending December 31, 2019 will require at least 7305 rows. This range will be used for the remainder of the post.

Numbers Table

If you do not have an existing table, we can quickly generate one:

While the following query will generate 10,000 rows, it can be limited to the desired range later. If you require more than 10,000 days, you can CROSS JOIN once more to produce 100,000 rows and so forth.

Calendar Table

This simple calendar table will contain basic extended information about the date:

Using the numbers table, the function TIMESTAMPADD is used to increment the date by day, beginning the day before January 1, 2000:

To obtain the desired range, use the LIMIT clause or a predicate if you do not wish to calculate the number of days in your range:

Using a LIMIT clause:

Using a predicate:

Following the LIMIT clause, the calendar table can be populated as follows:

The lower and upper ranges of the calendar table can be confirmed using MIN() and MAX():

This results in:

To populate extended information about the date, an UPDATE would need to be performed. This method is not efficient, and instead a sub-query should be used using built-in date/time functions in a single pass:

It’s easier to include extended information about the date in the calendar table at creation, rather than later and incur IO cost with an UPDATE.

Further Optimization

Replicating this small table will allow for local joins to occur:

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: