Field expression function reference

General functions

coalesce

Signature

coalesce(arg1, arg2, …​argN)

Description

The function returns the first non null value.

Example

coalesce(null, 0, 1, 2) results in 0

Number functions

min

Signature

min(arg1, arg2, …​argN)

Description

The function returns item with the lowest value.

Example

min(0, 1, 2) results in 0

max

Signature

max(arg1, arg2, …​argN)

Description

The function returns item with the highest value.

Example

max(0, 1, 2) results in 2

round

Signature

round(number, digits)

Description

The function rounds a floating point number to the specified number of digits.

Example

round(3.14, 1) results in 3.1

floor

Signature

floor(number)

Description

The function rounds number down to the nearest integer.

Example

floor(3.14) results in 3

ceil

Signature

ceil(number)

Description

The function rounds number up to the nearest integer.

Example

ceil(3.14) results in 4

Text functions

trim

Signature

trim(string)

Description

The function removes spaces at the beginning and at the end of the string.

Example

trim(" foo ") results in "foo"

trimLeft

Signature

trimLeft(string, character)

Description

The function removes character from the beginning of the string.

Example

trim(" foo ", " ") results in "foo "

trimRight

Signature

trimRight(string, character)

Description

The function removes character from the end of the string.

Example

trim(" foo ", " ") results in " foo"

toLower

Signature

toLower(string)

Description

The function converts all characters to lowercase.

Example

toLower("FOO") results in "foo"

toUpper

Signature

toUpper(string)

Description

The function converts all characters to uppercase.

Example

toUpper("foo") results in "FOO"

shortest

Signature

shortest(arg1, arg2, …​argN)

Description

The function returns the shortest string.

Example

shortest("foo", "foobar") results in "foo"

longest

Signature

longest(arg1, arg2, …​argN)

Description

The function returns the longest string.

Example

longest("foo", "foobar") results in "foobar"

format

See String formatting for available formatting options.

Signature

format(format, arg1, arg2, …​argN)

Description

Returns the formatted string.

Example

format("test %d", 10) results in "test 10"

Date time functions

strftime

See Date and time formatting for available formatting options.

Signature

strftime(datetime, format)

Description

The function returns DateTime string for the specified date and format.

Example

strftime(datefield, "%Y-%m-%d") results in "1970-01-01"

modTime

Signature

modTime(datetime, duration)

Description

The function returns modified DateTime string.

Example

modTime(datefield, "+30m") results in "1970-01-01T00:30:00"

parseISOTime

Signature

parseISOTime(datetime)

Description

The function returns parsed ISO DateTime string.

Example

parseISOTime(datefield) results in "1970-01-01T00:00:00+00:00"

parseDuration

Signature

parseDuration(duration)

Description

The function returns parsed duration.

Example

parseDuration("2h") results in "2h0m0s"

earliest

Signature

earliest(arg1, arg2, …​argN)

Description

The function returns earliest DateTime.

Example

earliest(datefield1, datefield2) results in "1970-01-01T00:00:00"

latest

Signature

latest(arg1, arg2, …​argN)

Description

The function returns latest DateTime.

Example

latest(datefield1, datefield2) results in "1970-01-01T00:30:00"

String formatting

%v

Description

Returns the raw value.

Example

format("%v", "test") results in "test"

%t

Description

Returns the boolean value as true/false.

Example

format("%t", true) results in "true"

%b

Description

Returns the number in base 2 (in the binary format).

Example

format("%b", 10) results in "1010"

%d

Description

Returns the number in base 10.

Example

format("%d", 10) results in "10"

%o

Description

Returns the number in base 8.

Example

format("%o", 10) results in "12"

%O

Description

Returns the number in base 8 with 0o prefix.

Example

format("%O", 10) results in "0o12"

%x

Description

Returns the number in base 16; lower-case a-f.

Example

format("%x", 10) results in "a"

%X

Description

Returns the number in base 16; upper-case A-F.

Example

format("%X", 10) results in "A"

%b

Description

Returns the floating point number in scientific notation with binary exponent.

Example

format("%b", 10.11) results in "5691424029089464p-49"

%e

Description

Returns the floating point number in scientific notation; lower-case e.

Example

format("%e", 10.11) results in "1.011000e+01"

%E

Description

Returns the floating point number in scientific notation; upper-case E.

Example

format("%E", 10.11) results in "1.011000E+01"

%f

Description

Returns the floating point number with a decimal point.

Example

format("%f", 10.11) results in "10.110000"

Date and time formatting

%Y

Description

Returns the year with century as a decimal number.

example

strftime(dateField, "%Y") results in "1993"

%y

Description

Returns the year without century as a decimal number (00-99).

example

strftime(dateField, "%y") results in "93"

%C

Description

Returns year / 100 as a decimal number; single digits are preceded by a zero.

example

strftime(dateField, "%C") results in "19"

%m

Description

Returns the month as a decimal number (01-12).

example

strftime(dateField, "%m") results in "02"

%B

Description

Returns the full national month name.

example

strftime(dateField, "%B") results in "February"

%b

Description

Returns the abbreviated national month name.

example

strftime(dateField, "%b") results in "Feb"

%U

Description

Returns the week number of the year (Sunday as the first day of the week) as a decimal number (00-53).

example

strftime(dateField, "%U") results in "05"

%V

Description

Returns the week number of the year (Monday as the first day of the week) as a decimal number (01-53).

example

strftime(dateField, "%V") results in "05"

%W

Description

Returns the week number of the year (Monday as the first day of the week) as a decimal number (00-53).

example

strftime(dateField, "%W") results in "05"

%A

Description

Returns the full national weekday name.

example

strftime(dateField, "%A") results in "Tuesday"

%a

Description

Returns the abbreviated national weekday name.

example

strftime(dateField, "%a") results in "Tue"

%d

Description

Returns the day of the month as a decimal number (01-31).

example

strftime(dateField, "%d") results in "02"

%e

Description

Returns the day of the month as a decimal number (1-31).

example

strftime(dateField, "%e") results in " 2"

%j

Description

Returns the day of the year as a decimal number (001-366).

example

strftime(dateField, "%j") results in "033"

%u

Description

Returns the weekday (Monday as the first day of the week) as a decimal number (1-7).

example

strftime(dateField, "%u") results in "5"

%w

Description

Returns the weekday (Sunday as the first day of the week) as a decimal number (0-6).

example

strftime(dateField, "%w") results in "2"

%H

Description

Returns the hour (24-hour clock) as a decimal number (00-23).

example

strftime(dateField, "%H") results in "06"

%k

Description

Returns the hour (24-hour clock) as a decimal number (0-23).

example

strftime(dateField, "%k") results in " 6"

%I

Description

Returns the hour (12-hour clock) as a decimal number (01-12).

example

strftime(dateField, "%I") results in "06"

%l

Description

Returns the hour (12-hour clock) as a decimal number (1-12).

example

strftime(dateField, "%l") results in " 6"

%M

Description

Returns the minute as a decimal number (00-59).

example

strftime(dateField, "%M") results in "00"

%S

Description

Returns the second as a decimal number (00-60).

example

strftime(dateField, "%S") results in "00"

%S

Description

Returns the millisecond as a decimal number (000-999).

example

strftime(dateField, "%S") results in "000"

%p

Description

Returns the national representation of either "ante meridiem" (a.m.) or "post meridiem" (p.m.).

example

strftime(dateField, "%p") results in "AM"

%c

Description

Returns the national representation of time and date.

example

strftime(dateField, "%c") results in "Tue Feb 2 06:00:00 1993"

%X

Description

Returns the national representation of the time.

example

strftime(dateField, "%X") results in "06:00:00"

%x

Description

Returns the national representation of the date.

example

strftime(dateField, "%x") results in "02/02/93"

%Z

Description

Returns the time zone name.

example

strftime(dateField, "%Z") results in "-0500"

%z

Description

Returns the time zone offset from UTC.

example

strftime(dateField, "%z") results in "-0500"

%n

Description

Returns a newline (\n).

example

strftime(dateField, "%n") results in "\n"

%t

Description

Returns a tab.

example

strftime(dateField, "%t") results in "\t"

%%

Description

Returns a %.

example

strftime(dateField, "%%") results in "%"

%F

Description

Equivalent to %Y-%m-%d.

example

strftime(dateField, "%F") results in "1993-02-02"

%D

Description

Equivalent to %m/%d/%y.

example

strftime(dateField, "%D") results in "02/02/93"

%R

Description

Equivalent to %H:%M.

example

strftime(dateField, "%R") results in "06:00"

%r

Description

Equivalent to %I:%M:%S %p.

example

strftime(dateField, "%r") results in "06:00:00 AM"

%T

Description

Equivalent to %H:%M:%S.

example

strftime(dateField, "%T") results in "06:00:00"

%v

Description

Equivalent to %e-%b-%Y.

example

strftime(dateField, "%v") results in " 2-Feb-1993"