Plain functions

Plain functions can be used in SELECT, OVER, WHERE, ORDER BY, GROUP BY and HAVING clauses in RAQL queries. They typically either change the data type of column values, extract part of the values, or transform values in some way.

Plain functions are applied individually to values of one row without access to values in other rows and they return a single value.

Function

Description

column_name(Any col_1, Any col_2, ..., Any col_n, Number index)

column_name(Any col)

Returns the column value at position index from the columns col_1, col_2, ..., col_n. The index will be rounded if not an integer value and is not required if only one column is used.

column_type(Any col_1, Any col_2, ..., Any col_n, Number index)

column_type(Any col)

Returns the column name at position index from the columns col_1, col_2, ..., col_n. The index will be rounded if not an integer value and is not required if only one column is used.

custom_month(String col-or-expr)

Extracts the month from dates in the format EEE MMM dd HH:mm:ss z yyyy and returns the number of the month as an integer. This is based on English month names.

dayOfWeek(Date col-or-expr)

dayOfWeek(String col-or-expr)

dayOfWeek(Long col-or-expr)

Converts the values for this column or expression to a date and returns the day of the week as an integer [1 - 7]. This number is specific to the locale for the MashZone NextGen Server. For example, day 1 is Sunday in the United States but Monday in France.

epoc_sec_to_date

Represents internally the Java notation of time as milliseconds since midnight, January 1, 1970 UTC.

  • Conversion of seconds (not milliseconds!) since above reference time to date

  • Three variants of function using either string, double, or long as input

Examples

  • epoc_sec_to_date(‘123456‘)

  • epoc_sec_to_date(123456.00)

  • epoc_sec_to_date(123456)

extract_date(Date dt, String field)

Extracts the portion of the date or time from values for this column or expression that is identified by the dt parameter. Valid field values are:

  • day

  • day-of-week

  • hour

  • minute

  • month

  • monthname

  • quarter

  • second

  • week

  • year

extract_hour(Date dt)

extract_hour(String dt)

extract_hour(Long dt)

Extracts the hour portion of the date and time in the column or expression that is identified by the dt parameter.

extract_minute(Date dt)

extract_minute(String dt)

extract_minute(Long dt)

Extracts the minute portion of the date and time in the column or expression that is identified by the dt parameter.

extract_month(Date dt)

extract_month(String dt)

extract_month(Long dt)

Extracts the month portion of the date and time in the column or expression that is identified by the dt parameter.

extract_second(Date dt)

extract_second(String dt)

extract_second(Long dt)

Extracts the second portion of the date and time in the column or expression that is identified by the dt parameter.

extract_week(Date dt)

extract_week(String dt)

extract_week(Long dt)

Extracts the week portion of the date and time in the column or expression that is identified by the dt parameter.

extract_year(Date dt)

extract_year(String dt)

extract_year(Long dt)

Extracts the year portion of the date and time in the column or expression that is identified by the dt parameter.

format_date(Date value)

format_date(Date value, String datePattern)

format_date(Date value, String datePattern, String timeZone)

Converts a date value to a string representation in given target format and target timezone.

  • datePattern may be 'short', 'medium', 'long', 'full' or any date pattern that is accepted by Java's SimpleDateFormat. The variant without datePattern parameter uses this pattern: yyyy-MM-dd''T''HH:mm:ss.SSSZ

  • timeZone may be any time zone ID that is understood by Java's TimeZone.getTimeZone method. For more details, see the corresponding Java documentation. Variants of this function without a timeZone parameter use the configured feed processing time zone.

Examples

  • format_date(value)

  • format_date(value, 'yyyy-MM-dd')

  • format_date(value, 'h:mm a', 'GMT-08:00')

  • format_date(value, 'HH:mm:ss', 'Europe/Berlin')

format_date (dateColumn, datePattern)

Corresponds to format_date(dateColum, datePattern, 'GMT')

format_date (dateColumn)

Corresponds to format_date(dateColum, 'yyyy-MM-dd'T'HH:mm:ss.SSSZ', 'GMT')

geo_distance(String column-or-lat1, String column-or-long1, String column-or-lat2, String column-or-long2)

Calculates the geographical distance between two locations identified by latitude and longitude. Commonly, one set of coordinates is provided from two columns or expressions in the data and the other is provided as literal values.

If any parameter value is null, this returns a null value.

matches(String col-or-expr, String pattern)

Determines if a string matching the regular expression in pattern exists anywhere within the values of the column or expression. Matching is case insensitive. Returns a boolean value (true or false).

For example:

where matches(firstname,'ra[n|l]')='true'

Will match Frank, Gerald or Randy in the firstname column. To perform exact regular expression matches, with no implicit wildcards, see the regex function.

nvl(String col-or-expr, String str)

nvl(Date col-or-expr, String str)

nvl(Number col-or-expr, String str)

Replaces null values in the specified column or expression with the specified string value.

Note: In previous releases, string columns with only white space characters (space, tab, etc.) were treated as a null value.

Effective in 3.8, columns with string values are considered to be null only if they have no value or they contain an empty string.

project(Xcol_1, X col_2, ..., X col_n, Number index)

Returns the value at position index from the columns col_1, col_2, ..., col_n of a common type X. The index will be rounded if not an integer value.

quarter(Date col-or-expr)

quarter(String col-or-expr)

quarter(Long col-or-expr)

Converts the values for this column or expression to a date and returns the number of the quarter as an integer.

regex(String col-or-expr, String pattern)

Determines if a string matching the regular expression in pattern exists in the values of the column or expression as specified. Matching is case insensitive. Returns a boolean value (true or false).

Note: This function does not add any implicit wildcards to the regular expression. To find a string anywhere within column values, use the matches function.

For example:

where regex(firstname,'ra[n|l]')=true

Will match Ralph or Randy in the firstname column, but will not match Frank or Gerald.

split_part(String col-or-expr, String delim, Integer part)

split_part(String col-or-expr, Character delim, Integer part)

Returns the nth part of the values of this column or expression when each value is split into parts at each delimiter defined in delim. For example:

split_part("INV:2012:GHI345",":",3)

Returns the string "GHI345".

split_regex((String col-or-expr, String pattern, Integer part)

This function is identical to split_part with the exception that the delimiter used to split the column values from pattern is a regular expression.

time_mask(Date column, String mask)

time_mask(String column, String mask)

Converts the value for this column or expression to a date and time, if needed, in the form yyyy-MM-dd:HH:mm:ss.

Note: If the values for the column do not contain times, the time is set to 00:00:00.

It extracts the portion of this date and time through the time part identified by the mask.

Valid masks include:

  • y = returns only the year.

  • M = returns the year and month.

  • d = returns the year, month and day.

  • H = returns the full date and hour.

  • m = returns the full date, hour and minute.

  • s = returns the full date and time.

to_date(String value)

to_date(String value, String datePattern)

to_date(String value, String datePattern, String timeZone)

Converts a string value to Date.

The datePattern, if present, is any pattern that is accepted by Java's SimpleDateFormat. If no pattern is specified, this function can have a negative effect on performance because it attempts to convert the string iterating over the following date formats:

  • yyyy-MM-dd HH:mm:ss.SSS

  • yyyy-MM-dd HH:mm:ss

  • yyyy-MM-dd'T'HH:mm:ss.SSSXXX

  • yyyy-MM-dd'T'HH:mm:ss.SSS

  • yyyy-MM-dd'T'HH:mm:ssXXX

  • yyyy-MM-dd'T'HH:mm:ss

  • M/dd/yy or the short format for the current locale

  • MMMM d, yyyy or the long format for the current locale

  • EEEE, MMMM d, yyyy or the full format for the current locale

  • EEE MMM d HH:mm:ss z yyyy

  • EEE, d MMM yyyy HH:mm:ss Z

  • dd.MM.yyyy HH:mm:ss

  • yyyy-MM-dd

  • dd-MM-yyyy

  • dd.MM.yy

  • MM/dd/yy

    Use the datePattern parameter to define the date and time pattern used in values for this column.

Note: For more information on date format patterns, see the Java SimpleDateFormat class.

The timeZone may be any time zone ID that is understood by Java's TimeZone.getTimeZone method. For more details, see the corresponding Java documentation. Variants of this function without a timeZone parameter use the configured feed processing time zone.

truncate(Number col-or-expr, Number decimals)

For numeric values or expressions, this truncates the number to the number of decimals places specified. If the decimals parameter is omitted, truncates the number to zero decimals.