Datetime Functions

For the purpose of this reference guide: Date refers only to year, months, days (and quarters, weeks) and their combinations. Time refers to hours, minutes and seconds and their combinations. For combination of date and time we strictly use datetime term.

See the Date and time functions inherited from transformation scripting.

Date strings

The date string parameters are entered absolutely (dd.MM.yyyy, yyyy-MM-dd or according to domain settings) or relatively (time variables) by operators:

date +|- n[d|w|m|q|y]

where

date is date in dd.MM.yyyy or yyyy-MM-dd format, or one of actualyear, actulamonth, actualquarter, actualweek, actualday, now
n represents the count of:
d days, w weeks, m months, q quarters or y year.

Examples:

'2011-01-01 + 1q - 2m + 1d' means Jan 1, 2011 + 1 quartal - two months + 1 day, that represents Feb 2, 2011
'actualYear + 1m + 1d' , (used on 8 Mar, 2011) means 1 Feb, 2011 + 1 month + 1 day, that represents 2 Feb, 2011 as well

Another way how to create the date strings is following:

date(day: dd, month: MM, year: YYYY)
date(d: dd, m: MM, y: YYYY)
date(week: w, year: YYYY)
date(quarter: q, year: YYYY)

Changing datetime context

What is the datetime context? Consider following example:

	01/2011	02/2011	03/2011
formula indicator	1000	1200	1300

Formula is evaluated for each column - in this example, in columns are values evaluated for particular months. During the evaluation of value 1000, the datetime context was 01/2011, then during the processing of value 1200, the context was 02/2011 etc.

Function	Description
`Object dateAt(String dateString, { expression })`	Changes the context of the evaluated expression to `dateString` date. Example: dateAt('2011-01-01', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; });
`Object dateInterval(String from, String to, { expression })`	Changes the date context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Example: dateInterval('2011-01-01', '2011-12-31' { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); Values for the indicators `M_NAME_1` and `M_NAME_2` are aggregated for the whole period.
`Object timeAt(String timeString, { expression })`	Changes the context of the evaluated expression to `timeString` date. Example: timeAt('13:33:23', { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; });
`Object timeInterval(String from, String to, { expression })`	Changes the time context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Example: timeInterval('00:00:01', '23:59:59' { i1 = M_NAME_1 i2 = M_NAME_2 if (i1 > i2) { return i1; } return i2; }); Values for the indicators `M_NAME_1` and `M_NAME_2` are aggregated for the whole period.
`Object dateAt (String date, String period) { expression })`	Changes the context of the evaluated expression to `dateString` date - aggregated by the defined period {DAY, D, WEEK, W, MONTH, M, YEAR, Y}.
`Object dateInterval (String from, String to, String period ) { expression })`	Changes the date context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Aggregation by the defined period {DAY, D, WEEK, W, MONTH, M, YEAR, Y} is also performed.
Object minus(String date, period, int)	Substracts the amount of specified `period` (years, months, weeks, days) to the defined date value. minus(date(firstValue('L_DATE')), 'days', 20) //subtracts 20 days from the date value
Object minus(String time, period, int)	Adds the amount of specified `period` (years, months, weeks, days) to the defined date value. minus(time(firstValue('L_TIME')), 'minutes', 30) //subtracts 30 minutes from the time value
Object plus(String date, period, int)	Adds the amount of specified `period` (years, months, weeks, days) to the defined date value. plus(date(firstValue('L_DATE')), 'days', 20) //adds 20 days to the date value
Object plus(String time, period, int)	Adds the amount of specified `period` (hours, minutes, seconds) to the defined date value. plus(time(firstValue('L_TIME')), 'seconds', 50) //adds 50 seconds to the time value
`Object timeAt (String time, String period) { expression })`	Changes the context of the evaluated expression to `timeString` time - aggregated by the defined period {HOUR, H, MINUTE, SECOND}.
`Object timeInterval (String from, String to, period) { expression })`	Changes the time context of the expression and evaluates it aggregated in the specified interval `from` - `to`. Aggregation by the defined period {HOUR, H, MINUTE, SECOND} is also performed.
`Object withoutDateTime() { expression })`	Evaluates the expression without date time interval.

Obsolete functions

These functions may be removed in further releases

Function	Description
`Object value(String dateString, String indicator)`	Loads the indicator's value at specified date.
`Object value(String dateString, int drill_down_level)`	Loads the indicator's value at specified date aggregated for N previous levels.
`Object value(String dateFrom, String dateTo, String indicator)`	Loads cumulative indicator's value for specified date interval.
`Object value(String dateFrom, String dateTo, String indicator, int dril_down_level)`	Loads cumulative indicator's value for specified date interval aggregated for N previous levels.

Advanced functions

Function

Description

Number cumulateFromDate(String startDate, String indicator)

This function gradually adds the current value to the cumulated value. Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`cumulateFromDate('2011-01-01', 'M_NAME_1')`	1000	2200	3500

Number cumulateFromTime(String startTime, String indicator)

This function gradually adds the current value to the cumulated value. Example:

	00:01	00:02	00:03
`M_NAME_1`	1000	1200	1300
`cumulateFromTime('00:01', 'M_NAME_1')`	1000	2200	3500

prev(String indicatorCode)

Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`prev('M_NAME_1')`		1000	1200

Number prev(String indicatorCode, int prevLevelAgg)

Value of the passed indicator calculated for previous date or time value (e.g. previous month, day, hour, ...). Parameter prevLevelAgg represents number of previous levels which should be aggregated.

Number next(String indicatorCode)

Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`next('M_NAME_1')`	1200	1300

Number next(String indicatorCode, int prevLevelAgg)

Value of the passed indicator calculated for next date or time value (e.g. previous month, day, hour, ...). Parameter prevLevelAgg represents number of previous levels which should be aggregated.

int daysBetween(DateTime dateFrom, DateTime dateTo)

Function calculates number of days between provided dates.

int daysBetween(DateTime dateFrom, DateTime dateTo, int[])

Function calculates number of days between provided dates and excludes selected day(s) of the week. Day of the week is referenced by number: 1 is Monday, 7 is Sunday.

Following example shows, how Saturdays and Sundays can be excluded from the calculation:

daysBetween(date(firstValue('L_DATE_FROM'),date(firstValue('L_DATE_TO'), 6,7)

int daysBetween(LocalDate dateFrom, LocalDate dateTo)

Function calculates number of days between provided dates.

int monthsBetween(DateTime dateFrom, DateTime dateTo)

Function calculates number of months between provided dates.

int monthsBetween(LocalDate dateFrom, LocalDate dateTo)

Function calculates number of months between provided dates.

int yearsBetween(DateTime dateFrom, DateTime dateTo)

Function calculates number of years between provided dates.

int yearsBetween(LocalDate dateFrom, LocalDate dateTo)

Function calculates number of years between provided dates.

For changing the whole context of the evaluated expression, you can use following functions:

Number prev(String period, { expression })

Changes the context of the expression to desired previous date period. The value of the period parameter is one of: DAY, WEEK, MONTH, QUARTER, YEAR. Example:

	01/2011	02/2011	03/2011
`M_NAME_1`	1000	1200	1300
`prev(MONTH) { M_NAME_1 }`		1000	1200

Number next(String period, { expression })

Changes the context of the expression to desired next date period. The value of the period parameter is one of: DAY, WEEK, MONTH, QUARTER, YEAR

The following example works with values, which are loaded for one year before the actual table/chart datetime entry:

return prev(YEAR) {
  M_NAME_1 / M_NAME_2 * 100
}

You may simply enter only the first letter of time unit (in case of time context changing formulas) instead of their full names (Y,Q,M,W,D), for example prev(Y){}

Nested expressions

Date and time functions can be combined (if applicable), eg.: Value of M_INDICATOR at 8th December 2010, 9:04:02AM:

dateAt ('2010-12-08') { timeAt('09:04:02') { M_INDICATOR } }

Page tree