Time functions

The application allows you to define formulas, in virtual meters, widgets or alerts. These can be used to specify calculations that will be performed dynamically using stored data from physical meters.

Among the functions available, some allow you to use or manipulate the time dimension of meter data.

Time offset

TIMESHIFT(expression, offset, align_week_days)

Returns the values of an expression, over a time range offset from the selected time range.

The offset can be expressed as:

• seconds: seconds

• minutes: minutes

• hours: hours

• days: days

• months: months

• years: years

The optional parameter align_days_of_the_week, true (true) or false (false, default), is used to add an additional offset so as to align the days of the week. It can be used when there is an offset in months or years, to have a more relevant comparison, for example when consumption depends on working days.

 

Examples

TIMESHIFT(meter, -1 month)
Returns the meter values to the previous month, thus comparing them with those of the selected month:

TIMESHIFT(3*meter, -2 years, true)
Returns the meter value three times 2 years previously by aligning the days of the week.

meter - TIMESHIFT(meter, -1 day)

Calculates the difference between the value of the meter and that on the previous day.

It is not currently possible to use multiple encapsulated TIMESHIFT. The result will be incorrect.
❌TIMESHIFT(TIMESHIFT(meter, -1 year), -1 day)

✅TIMESHIFT(meter, -366 day)

HOUR_BEFORE(expression)

Returns the values of the hour preceding the selected time range.

DAY_BEFORE(expression)

Returns the values for the day before the selected time range.

WEEK_BEFORE(expression)

Returns the values for the week preceding the selected time range.

MONTH_BEFORE(expression, align_week_days)

Returns the values for the month preceding the selected time range.

The optional parameter align_days_of_the_week, true (true) or false (false, default), allows you to add an additional offset so as to align the days of the week. It can be to have a more relevant comparison, for example when consumption depends on working days.

YEAR_BEFORE(expression, align_week_days)

Returns the values for the year preceding the selected time range.

The optional parameter align_days_of_the_week, true (true) or false (false, default), allows an additional offset to be added so as to align the days of the week. It can be used to have a more relevant comparison, for example when consumption depends on working days.

LAG(expression, n)

Returns the data from the expression, offset by n points (in the past). Unlike the TIMESHIFT function, it does not return values outside the selected time page. Consequently, the first n points returned will be missing (null).

Exemple

IF(meter != LAG(meter, 1), 1, 0)
Returns 1 if the meter has changed value from the previous point, otherwise returns 0.

Other functions

DATE()

Returns the date of the points. The result of this function can be used to compare the date of the point to the current date, or to a fixed value.

 

Examples

IF(DATE() < '2017-06-01', meter / 2, meter)
The meter data prior to 1 June 2017 will be divided by 2.

IF(DATE() > NOW(), consumption forecast, 0)

Ignores meter data consumption forecast prior to the current date.

IF(DATE() < TODAY(), 1, 0)
Returns 1 if the point date is earlier than the current date, otherwise returns 0.

NOW()

Returns the current date and time.

TODAY()

Returns the current day's date.

DATE_PART(part_of_date_to_extract)

Returns the requested date part.
The date elements available are as follows:

• 'day' (day of the month, between 1 and 31)
• 'doy' (day of the year, between 0 and 366)
• 'hour'
• 'minute'
• 'second'
• 'isodow' (day of the week: 1 for Monday, 7 for Sunday)
• 'year'
• 'isoyear' (year number according to ISO 8601 standard)
• 'month' (month between 1 and 12)
• 'quarter' (quarter between 1 and 4)
• 'week' (number of the week according to the ISO 8601 standard)

 

Examples

IF( DATE_PART('hour') >= 21 OR DATE_PART('hour') < 6 , meter, 0)
Returns the value of the meter if the point is between 9pm and 6am, otherwise returns 0.

IF (DATE_PART('isodow') < 6, meter, null)

Returns meter values ignoring weekends (Saturday and Sunday).