Function reference

How to use functions within the formula node in workflows.

abs( number )

Calculate the absolute value of a numeric value.

Arguments:

number: a numeric scalar or column

add( x, y )

Adds two numeric values together.

Arguments:

x: a numeric scalar or column
y: a numeric scalar or column

and( value, ... )

Logical and operaion

Arguments:

value: boolean scalars or columns

between( value, lower, upper )

Check if value falls between the lower/upper bounds.

Arguments:

value: a scalar or column
lower: a scalar or column representing the lower bound
upper: a scalar or column representing the upper bound

convert( value, type )

Change the type of a value to a different data type.

Arguments:

value: a scalar or column
type: the type to convert to, accepts `text`, `float`, `int`, `str`, `time`, `date` and `timestamp`

ceiling( number )

Round a numeric value up to the nearest integer value greater than or equal to this value.

Arguments:

number: a numeric scalar or column

coalesce( value, ... )

Returns the first non-null value from the passed arguments in left-to-right order

Arguments:

value: scalars or columns, uses the first not null record

contains( text, pattern )

Determine if text exactly contains the given pattern.

Arguments:

text: a text scalar or column that should contain pattern
pattern: a text scalar or column that should be in text

date( year, month, day )

Create a date from year, month and day

Arguments:

year: a integer scalar or column providing the year
month: a integer scalar or column providing the month
day: a integer scalar or column providing the day

time( hour, minute, second )

Create a time from hour, minute and second

Arguments:

hour: a integer scalar or column providing the hour
minute: a integer scalar or column providing the minute
second: a integer scalar or column providing the second

extract_date( date )

Get date part from a timestamp value

day( date )

Get the day of month from a date or timestamp value

Arguments:

date: a timestamp scalar or column

day_of_week( date )

Get day of the week from date or timestamp, starting from 1 Sunday to 7 Saturday

Arguments:

date: a timestamp scalar or column

divide( dividend, divisor )

Divide dividend by divisor

Arguments:

dividend: a numeric scalar or column
divisor: a numeric scalar or column

epoch_seconds( timestamp )

Seconds passed since 00:00:00 UTC on 1 January 1970 also called UNIX time

Arguments:

timestamp: a timestamp scalar or column

exp( value )

Calculate exponential value

Arguments:

value: a scalar or column

find( text, searchtext )

Returns position (0 indexed) of first occurence of searchtext in text

Arguments:

text: a text scalar or column
searchtext: a text scalar or column

floor( number )

Rounding down a numeric value to the greatest integer less or eqqual

Arguments:

number: a numeric scalar or column

fillnull( value, fill_value )

Replace nulls in value with fill_value

Arguments:

value: a scalar or column
fill_value: a scalar or column

hash( value )

Produces a cryptographic fingerprint from value using the Fingerprint64 method

Arguments:

value: a scalar or column

hour( time )

Get the hour from date or timestamp

Arguments:

time: a time scalar or column

ifelse( condition, true_value, false_value )

Returns the first value if condition is true if not it returns the second value

Arguments:

condition: a condition of a boolean column, scalar or another formula
true_value: scalar or column that is return if condition is true
false_value: scalar or column that is return if condition is false

isnull( value )

Check whether value is null

Arguments:

value: a scalar or column

join( delimiter, text, ... )

Concatenate text with the delimiter

Arguments:

delimiter: a value that is used to separate the following arguments
text: a text scalar or column

json_extract( json, path ) - coming soon 🚀

Extract a field inside JSON, returns text

Arguments:

json: a text value in JSON format
path: path to the desired item in the format e.g. `$.a.b[0]`

left( text, nchars )

Return up to nchars characters starting from start of each text

Arguments:

text: a text scalar or column
nchars: a numeric scalar or column indicating the number of characters from left that are returned

length( text )

Calculate character length from text

Arguments:

text: a text scalar or column

like( text, pattern )

Compare text with another pattern, returns True if pattern is like text

Arguments:

text: a text scalar or column
pattern: a text scalar or column

ln( number )

Returns the natural logarithm of number

Arguments:

number: a numeric scalar or column

log( number, base )

Returns the logarithm with base of number

Arguments:

number: a numeric scalar or column
base: a numeric scalar or column

log2( number )

Returns the logarithm with base 2 of number

Arguments:

number: a number scalar or column

log10( number )

Returns the logarithm with base 10 of number

Arguments:

number: a numeric scalar or column

lower( text )

Turns text to lowercase

Arguments:

text: a text scalar or column

lpad( text, length [, fillchar] )

Returns string of given length by truncating (on left) or padding (on left) original string

Arguments:

text: a text scalar or column
length: a numeric scalar or column indicating the length of the resulting text
fillchar: a text scalar or column

ltrim( text )

Remove white space on the left of text

Arguments:

text: a text scalar or column

millisecond( time )

Get the milliseconds from date or timestamp

Arguments:

time: a time scalar or column

minute( time )

Get the minute from date or timestamp

Arguments:

time: a time scalar or column

month( date )

Get the month from date or timestamp

Arguments:

date: a timestamp scalar or column

modulo( x, y )

Compute the modulo of x and y

Arguments:

x: an integer scalar or column
y: an integer scalar or column

product( x, y )

Multiply x and y

Arguments:

x: a numeric scalar or column
y: a numeric scalar or column

notnull( value )

Check whether value is null

Arguments:

value: a scalar or column

now( )

Compute the current datetime

or( value, ... )

Logical or operaion

Arguments:

value: boolean scalars or columns

power( number, exponent )

Calculate the number to the power of exponent

Arguments:

number: a numeric scalar or column
exponent: a numeric scalar or column

regex_extract( text, pattern, index )

👉 Read our in depth guide on regular expressions

Returns specified index, 0 indexed, from text based on regex pattern given

Arguments:

text: a text scalar or column
pattern: a text scalar or column
index: a numeric scalar or column

regex_replace( text, pattern, replace )

👉 Read our in depth guide on regular expressions

Replaces match found in text by regex pattern with replace text.

Arguments:

text: a text scalar or column
pattern: a text scalar or column that is being replaced by
replace: a text scalar or column that replaces the pattern in text

regex_search( text, pattern )

👉 Read our in depth guide on regular expressions

Search regex pattern in text

Arguments:

text: a text scalar or column
pattern: a text scalar or column

repeat( text, n )

Repeat text n times

Arguments:

text: a text scalar or column
n: a numeric scalar or column

replace( text, pattern, replace )

Replaces each exact occurrence of pattern in text with replace

Arguments:

text: a text scalar or column
pattern: a text scalar or column
replace: a text scalar or column that replaces the pattern in text

reverse( text )

Reverse text character order

Arguments:

text: a text scalar or column

right( text, nchars )

Return up to nchar characters starting from end of each text.

Arguments:

text: a text scalar or column
nchars: a numeric scalar or column indicating the number of characters from right that are returned

round( number, digits )

Round number to digit decimal positon

Arguments:

number: a numeric scalar or column
digits: an integer scalar or column

rpad( text, length [, fillchar] )

Returns string of given length by truncating (on right) or padding (on right) original string

Arguments:

text: a text scalar or column
length: a numeric scalar or column
fillchar: a numeric scalar or column

rtrim( text )

Remove white space on the right of text

Arguments:

text: a text scalar or column

second( time )

Get the seconds from time or timestamp

Arguments:

time: a time scalar or column

sqrt( number )

Calculate the square root

Arguments:

number: a numeric scalar or column

parse_date( value, format )

👉 Read our in depth guide on parsing

Creates a date from a provided text and format

Arguments:

value: a text scalar or column in the representing a date in `format`
format: a text scalar or column defining the date format of `value` e.g '%Y-%m-%d'

parse_time( value, format )

👉 Read our in depth guide on parsing

Creates a time from a provided text and format

Arguments:

value: a text scalar or column in the representing a time in `format`
format: a text scalar or column defining the time format of `value` e.g '%Y-%m-%d'

parse_datetime( value, format )

👉 Read our in depth guide on parsing

Creates a datetime from a provided text and format

Arguments:

value: a text scalar or column in the representing a datetime in `format`
format: a text scalar or column defining the datetime format of `value` e.g '%Y-%m-%d'

format_datetime( datetime, format )

👉 Read our in depth guide on formatting

Format timestamp into string with given format

Arguments:

datetime: a datetime scalar or column
format: determines how the resulting text is formatted

trim( text )

Remove white space surrounding the text

Arguments:

text: a text scalar or column

to_json_string( dictionary )

Transform a dictionary to a JSON string

Arguments:

dictionary: a dictionary column or scalar

to_timezone( datetime, timezone )

Convert a date & time to a new date & time in a different timezone

Arguments:

datetime: a date & time scalar or column
timezone: the time zone for the new column in tz format e.g. 'US/Pacific' (see a list here)

subtract( minuend, subtrahend )

Substract subtrahend from minuend

Arguments:

minuend: a numeric scalar or column
subtrahend: a numeric scalar or column

subtract_days( date, days )

Substract days from date

Arguments:

date: a date scalar or column
days: an integer scalar or column of days to subtract

substitute( text, pattern [, replace] [, else] )

Replace text if with replace if pattern is equal to text optional replace with else

Arguments:

text: a text scalar or column
pattern: a text scalar or column
replace: a text scalar or column
else: a text scalar or column

extract_time( datetime )

Get the time from a timestamp

Arguments:

datetime: a datetime scalar or column

datetime_seconds( seconds, unit )

Create a datetime from an integer as the number of unit since from January 1st, 1970 UTC (unix time)

Arguments:

seconds: an integer scalar or column
unit: "s" for seconds, "ms" for milliseconds, "us" for microseconds

datetime_diff( minuend, subtrahend, unit )

Get the difference between minuend and subtrahend in the given unit

Arguments:

minuend: a datetime scalar or column
subtrahend: a datetime scalar or column
unit: unit of the difference e.g. "s" for seconds

today( )

Compute today's date

truncate( datetime, unit )

Zero out smaller-size units beyond indicated unit

Arguments:

datetime: a datetime scalar or column
unit: unit e.g. "s" for seconds

upper( text )

Capitalize text.

Arguments:

text: a text scalar or column

weekday( date )

Gets weekday from a date

Arguments:

date: a timestamp scalar or column

year( date )

Get the year from date

Arguments:

date: a timestamp scalar or column