WithinC and WithinR functions

The WithinC and WithinR functions perform calendar-based queries, converting among time units and doing the calendar math to extract periods of interest from a time series value.

Syntax

WithinC(ts            TimeSeries, 
         tstamp        datetime year to fraction(5), 
         interval      lvarchar, 
         num_intervals integer, 
         direction     lvarchar) 
returns TimeSeries;

WithinR(ts            TimeSeries, 
         tstamp        datetime year to fraction(5), 
         interval      lvarchar, 
         num_intervals integer, 
         direction     lvarchar) 
returns TimeSeries;

ts

The source time series.

tstamp

The timepoint of interest.

interval

The name of an interval: second, minute, hour, day, week, month, or year.

num_intervals

The number of intervals to include in the output.

direction

The direction in time to include intervals. Possible values are:

FUTURE, or F, or f
PAST, or P, or p

Description

Every time series has a calendar that describes the active and inactive periods for the time series and how often they occur. A regular time series records one value for every active period of the calendar. Calendars can have periods of a second, a minute, an hour, a day, a week, a month, or a year. Given a time series, you might want to pose calendar-based queries on it, such as, “Show me all the values in this daily series for six years beginning on May 31, 2004,” or “Show me the values in this hourly series for the week including December 27, 2010.”

The Within functions are the primary mechanism for queries of this form. They convert among time units and do the calendar math to extract periods of interest from a time series value. There are two fundamental varieties of Within queries: calibrated (WithinC) and relative (WithinR).

WithinC, or within calibrated, takes a time stamp and finds the period that includes that time. Weeks have natural boundaries (Sunday through Saturday), as do years (January 1 through December 31), months (first day of the month through the last), 24-hour days, 60-minute hours, and 60-second minutes. WithinC allows you to specify a time stamp and find the corresponding period (or periods) that include it.

For example, July 2, 2010, fell on a Friday. Given an hourly time series, WithinC allows you to ask for all the hourly values in the series beginning on Sunday morning at midnight of that week and ending on Saturday night at 11:59:59. Of course, the calendar might not mark all of those hours as active; only data from active periods is returned by the Within functions.

WithinR, or within relative, takes a time stamp from the user and finds the period beginning or ending at that time. For example, given a weekly time series, WithinR can extract all the weekly values for two years beginning on June 3, 2008. WithinR is able to convert weeks to years and count forward or backward from the supplied date for the number of intervals requested. Relative means that you supply the exact time stamp of interest as the begin point or end point of the range.

WithinR behaves slightly differently for irregular than for regular time series. With regular time series, the time stamp argument is always mapped to a timepoint in accordance with the argument time series calendar interval. Relative offsetting is then performed starting with that point.

In irregular time series, the corresponding calendar interval does not indicate where time series elements are, and therefore offsetting begins at exactly the time stamp specified. Also, since irregular elements can appear at any point within the calendar time interval, WithinR returns elements with time stamps up to the last instant of the argument interval.

For example, assume an irregular time series with a daily calendar turning on all weekdays. The following function returns elements in the following interval (excluding the endpoint):

WithinR(stock_data, '2010-07-11 07:37:18', 'day', 3, 'future')
[2010-07-11 07:37:18, 2010-07-14 07:37:18]

In a regular time series, the interval is as follows, since each timepoint corresponds to the period containing the entire following day:

[2010-07-11 00:00:00, 2010-07-13 00:00:00]

Both functions take a time series, a time stamp, an interval name, a number of intervals, and a direction.

The supplied interval name is not required to be the same as the interval stored by the time series calendar, but it cannot be smaller than that interval. For example, given an hourly time series, the Within functions can count forward or backward for hours, days, weeks, months, or years, but not for minutes or seconds.

The direction argument indicates which periods other than the period containing the time stamp should be included; if there is only one period, the direction argument is moot.

For both WithinC and WithinR, the requested timepoint is included in the output.

Returns

A new time series with the same calendar as the original, but containing only the requested values.

Example

The following query retrieves data from the calendar week that includes Friday, January 4, 2011:

select WithinC(stock_data, '2011-01-04 00:00:00.00000', 
      'week', 1, 'PAST')
   from daily_stocks
   where stock_name = 'IBM';

The query returns the following results:

(expression)

origin(2011-01-03 00:00:00.00000),calend ar(daycal),
container(),threshold(20),re
gular,[(356.0000000000,310.0000000000,340.000000000,
999.0000000000),(156.000000
0000,110.0000000000,140.0000000000,111.0000000000), NULL,
(99.00000000000,54.000 00000000,66.00000000000,
888.0000000000)]

The following query returns two weeks' worth of stock trades starting on January 4, 2011, at 9:30 a.m.:

select WithinR(activity_data, '2011-01-04 09:30:00.00000', 'week', 2, 'future')
    from activity_stocks
    where stock_id = 600;

The following query returns the preceding three months' worth of stock trades:

select WithinR(activity_data, '2011-02-01 00:00:00.00000',
      'month', 3, 'past')
    from activity_stocks
    where stock_id = 600;