Relative Intervals and Datetimes (version 24)

All of Canary's client tools (Axiom, Excel Add-in, ODBC, API) support the functionality of relative datetime when requesting data for a given start/end time as well as the aggregate interval. For example, an interval of one hour could be expressed as '1:00:00', '1hour', or '1h'. A start time at the beginning of the day could be '00:00:00', 'day', or 'd'.

The following explains how relative intervals and datetimes are used.

Relative Interval

Expression:

(multiplier)(relative unit) +- (any number of additional relative intervals)

Examples:

1 day = 24 hours, or 23/25 hours on days that cross daylight saving boundaries
2 hours = 2 hours
2 hours + 15 minutes = 2 hours and 15 minutes
2 hours + 15 minutes - 30 seconds = 2 hours and 14 minutes and 30 seconds

Relative Units:

A relative unit can be expressed as any string value that matches the start of the keyword. For example, any of the following terms could be used to express minutes:

m = minutes
min = minutes
minute = minutes
minutes = minutes

Be aware that "m" would match on "minute" and "month". Minute is used because it is the first keyword matched. Make sure the desired keyword is matched when abbreviating.

The table below shows all options for relative units.

Relative DateTime

Expression:

(start term or function) +- (any number of additional relative intervals)

Examples:

The following results assume a current time of 12:34:56:

with start terms:
now = 12:34:56 today
now - 1 day = 12:34:56 yesterday
day + 20 hours = 20:00 today
tuesday = 0:00:00 tuesday of the current week
hour + 15 minutes = 12:15:00 today
hour + 15 minutes - 30 seconds = 12:14:30 today

with start functions:
second(15) = 12:34:45 today
second(15) - 1m = 12:33:45
minute(5) = 12:30:00 today
minute(20) - 1h = 11:20:00 today
time(8:00) = 8:00 today
time(20:00) = 20:00 yesterday (because 20:00 did not occur yet today)
time(8:00) + 8 hours = 16:00 today
shifts(4:00, 16:00) = 4:00 today
shifts(4:00, 12:00, 20:00) = 12:00 today
shifts(4:00, 16:00) - 12 hours = 16:00 yesterday

Start Terms:

A start term can be expressed as any string value that matches the start of the keyword. For example, any of the following terms could be used to express minute:

m = minute
min = minute
minute = minute

Be aware that "m" would match on "minute", "month", and "monday". Minute is used because it is the first keyword matched. Make sure the desired keyword is matched when abbreviating.

The table below shows all options for start terms.

Start Functions:

A start function can be expressed as any string value that matches the start of the keyword. For example, any of the following terms could be used to express minute:

m(15) = normalizes to 15 minute interval
min(15) = normalizes to 15 minute interval
minute(15) = normalizes to 15 minute interval

The table below shows all options for start functions.

Shift Schedules

If we wanted to build an Axiom application to display data from different shifts, we could do it in 2 ways assuming the following shift schedule:

1st shift: 7:00 - 15:00
2nd shift: 15:00 - 23:00
3rd shift: 23:00 - 7:00

1. Current/Previous:

current shift: (ValueBox properties)
AggregateStartTime = shifts(7:00, 15:00, 23:00)
AggregateInterval = 8 hours

previous shift: (ValueBox properties)
AggregateStartTime = shifts(7:00, 15:00, 23:00) - 8 hours
AggregateInterval = 8 hours

2. 1st/2nd/3rd:

In this example the shift values will reset when the 1st shift starts. 2nd/3rd shift will be empty until we enter into those shifts. We can use "time(7:00)" as an anchor time so all the shifts will reset at the same time.

1st shift: (ValueBox properties)
AggregateStartTime = time(7:00)
AggregateInterval = 8 hours

2nd shift: (ValueBox properties)
AggregateStartTime = time(7:00) + 8 hours
AggregateInterval = 8 hours

3rd shift: (ValueBox properties)
AggregateStartTime = time(7:00) + 16 hours
AggregateInterval = 8 hours

More Examples:

Start Terms:

Start Terms Abbreviated:

The following is a list of the shortest abbreviations possible to get the desired start term. Some abbreviations are longer because if the abbreviation was any shorter, it would match on a different start term.

• "Second", "Sunday", and "Saturday" all start with "S"; "Second" is listed first so it will be matched for "S".
• "Minute", "Month", and "Monday" all start with "M"; "Minute" is listed first so it will be matched for "M".
• "Month" and "Monday" both start with "Mon"; "Month" is listed first so it will be matched for "Mon".
• "Today", "Tuesday", and "Thursday" all start with "T"; "Today" is listed first so it will be matched for "T".
• "Yesterday" and "Year" both start with "Y"; "Yesterday" is listed first so it will be matched for "Y".
• "Yesterday" and "Year" both start with "Ye"; "Yesterday" is listed first so it will be matched for "Ye".
• "Week" and "Wednesday" both start with "W", "Week is listed first so it will be matched for "W".
• "Week" and "Wednesday" both start with "We", but "Week" is listed first so it will be matched for "We".

Abbreviated terms can be used, but watch out for collisions in abbreviations. Make sure you aren't abbreviating to a start term other than what you expect.

Start Terms With Math:

Start Functions:

Shift Functions:

Start Functions With Math:

Shift Functions With Math: