Relative DateTimes (version 22)

All of Canary's client tools (Axiom, Excel Add-in, ODBC, API) support the functionality of relative date/time when requesting data for a given start/end time as well as the aggregate interval. For example, if wanting to request 5-minute data from the beginning of the day until now, you would use

start: "day" (or "d")

end: "now"

aggregate interval: "5minute" (or "5m")

The format for the relative time is: keyword+-offset+-offset where keyword and offset are as specified in the table below. White space is ignored. The time string must begin with a keyword. Each offset must be preceded by a signed integer that specifies the number and direction of the offset. If the integer preceding the offset is unsigned, the value of the preceding sign is assumed positive. (The beginning default sign is positive). The keyword refers to the beginning of the specified time period. DAY means the timestamp at the beginning of the current day (00:00 hours, midnight), MONTH means the timestamp at the beginning of the current month, etc.

For example, DAY -1D+7H30M would represent the start time for data request for a daily report beginning at 7:30 in the morning of the previous day.

DAY = the first timestamp for today

-1D = the first timestamp for yesterday

+7H30M = 7:30 a.m. yesterday

Similarly, MO-1D+5h would be 5 a.m. on the last day of the previous month, NOW-1H15M would be an hour and fifteen minutes ago, and YEAR+3MO would be the first timestamp of April 1 this year.

In handling a gap in the calendar (due to different numbers of days in the month, or in the year), when one is adding or subtracting months or years:

• Month: if the answer falls in the gap, it is backed up to the same time of day on the last day of the month.

• Year: if the answer falls in the gap (February 29), it is backed up to the same time of day on February 28.

Note that the above does not hold for cases where one is adding or subtracting weeks or days, but only when adding or subtracting months or years, which may have different numbers of days in them.

Aggregate Intervals can be absolute (00:01:00) or relative (1m). Intervals of 1 hour or greater can be affected by Daylight Savings Time changes. One day in the spring will have 23 hours and one day in the fall will have 25 hours. If an interval is passed as relative then the timespan is passed to the Views Service as a negative timespan which does special "relative calculations" to account for transition in Daylight Saving Time and months.

• Relative aggregate intervals can use the short, offset notation and imply a single unit if not specified (d = 1d).
• Requesting an aggregate with a start time of YEAR-1Y and an end time of YEAR with an aggregate interval of MONTH will return a value for the 1st of each month over the previous year.

Keywords and Offsets are not case-sensitive.