CalendarPattern data type

The CalendarPattern data type defines the interval duration and the pattern of valid and invalid intervals in a calendar pattern.

The CalendarPattern data type is an opaque data type that has the following format:

{ num_intervals { on | off } }, interval

 

Table 1. CalendarPattern data type parameter values
Value Description
interval For regular time series, defines the size of one element. One of the following interval names:
  • Second
  • Minute
  • Hour
  • Day
  • Week
  • Month
  • Year

For irregular time series and hertz time series, the interval is significant only in defining on and off periods. The interval does not determine the number of elements. For hertz time series, the effective interval for recording data is the value of the hertz parameter when the time series is created.
num_intervals A positive integer that represents the number of interval units. Interval units are either valid intervals for time series data, if followed by on, or invalid intervals for time series data, if followed by off. The maximum number of interval units, either on or off, in a calendar pattern is 2035. Internal calculations take longer to perform if you use a long calendar pattern.

For compressed time series, the off keyword is not allowed.

Usage

The information inside the braces is the pattern specification. The pattern specification has one or more elements that consist of n, the number of interval units, and either on or off, to signify valid or invalid intervals. Elements are separated by commas. The information after the braces is the interval size.

The calendar pattern length is how many intervals before the calendar pattern starts over; after all timepoints in the pattern specification are exhausted, the pattern is repeated. For this reason, a weekly calendar pattern with daily intervals must contain exactly seven intervals and a daily calendar pattern with hourly intervals must contain exactly 24 intervals. If your calendar pattern length is not correct, your time series data might not match your requirements. For example, the pattern {1 off, 4 on, 1 off} appears to repeat every week, but the pattern repeats every six days because there are only six intervals. When the calendar pattern begins is specified by the calendar pattern start date.

Calendars that have the same calendar pattern length but different interval sizes are not equivalent. For example, a calendar can be built around a normal five-day work week, with the time unit in days, and Saturday and Sunday as days off. Assuming that the calendar pattern start date is for a Sunday, the syntax for this calendar pattern is: 

INSERT INTO CalendarPatterns
  VALUES('workweek_day',
        '{1 off, 5 on, 1 off}, day');

In the next example, the calendar is built around the same five-day work week, with the time unit in hours:

INSERT INTO CalendarPatterns
  VALUES('workweek_hour',
        '{ 32 off, 9 on, 15 off, 9 on, 15 off, 9 on, 15 off, 
           9 on, 15 off, 9 on, 31 off }, hour'};

Both examples have a calendar pattern length of seven days, or one week. However, the number of allowed records for regular time series and the on and off periods are different between the calendar patterns. The workweek_day calendar pattern allows a maximum of five records per week for regular time series and prohibits records on weekends. The workweek_hour calendar pattern allows 45 records per week for regular time series and prohibits records on weekends and 15 hours per day. For irregular time series, the number of on periods and the number of records do not correspond.

The calendar pattern is stored in the CalendarPatterns table and can be used in multiple calendars.

When a calendar is inserted into the CalendarTable table, it draws information from the CalendarPatterns table. The database server refers only to CalendarTable for calendar and calendar pattern information; changes to the CalendarPatterns table have no effect unless CalendarTable is updated or recreated.

You can manage exceptions to your calendar pattern by hiding elements for which there is no data by using the HideElem function.

Calendar patterns can be combined with functions that form the Boolean AND, OR, and NOT of the calendar patterns. The resulting calendar patterns can be stored in a calendar pattern table or used as arguments to other functions.

You can use the calendar pattern interval with the WithinR and WithinC functions to search for data around a specified timepoint. The WithinR function performs a relative search. Relative searches search forward or backward from the starting timepoint, traveling the specified number of intervals into the future or past. The WithinC function performs a calibrated search. A calibrated search proceeds both forward and backward to the interval boundaries that surround the given starting timepoint.

Examples

The following statement creates a pattern that is named hour that has a timepoint every hour:

INSERT INTO CalendarPatterns
			VALUES('hour', '{1 on} hour');

The following statement creates a pattern that is named fifteen_min that has a 15-minute timepoint:

INSERT INTO CalendarPatterns
			VALUES('fifteen_min', '{1 on, 14 off} minute');

The following statement creates a pattern that is named fourday_day that has a weekly pattern of four days on and three days off:

INSERT INTO CalendarPatterns
  VALUES('fourday_day',
        '{1 off, 4 on, 2 off}, day');