List of functions

DPQL Reference
Function Description
DPQL_ALIAS
DPQL_CONCAT Concatenates multiple expressions together. The function will take strings or outputs and combine them together, side by side. One example could be to CONCAT a users "Door Number", "Street", "City" and "ZIP/Postal Code" as separate fields into a "Full Address" field.
DPQL_COUNT([condition]) Counts the total number of rows matched by the query or current group. If a condition is provided, counts the number of rows in the query or current group which match the condition.
DPQL_COUNT_DISTINCT(expression) Counts the total number of distinct values for the given expression within the rows matched by the query or current group.
DPQL_CUR_DATE() Gets the current date in the time zone of the agent running the query.
DPQL_CUR_TIME() Gets the current time in the time zone of the agent running the query.
DPQL_DATE
DPQL_DATE_OFFSET_GROUP(seconds) Groups a date offset/difference in seconds into human-readable ranges(0–15 min, 15–30 min, etc).
DPQL_DATE_OFFSET_GROUP(to date, from date) Displays the difference between two provided dates as human-readable range.
DPQL_DAYNAME Returns the weekday name for a date value
DPQL_DAYOFMONTH Returns the day of month for a date value as an integer between 1-31
DPQL_DAYOFWEEK Returns the day of week for a date value as an integer between 1-7
DPQL_FORMAT(value, format) Formats the value into the specified format. Possible formats include: boolean, number, numberraw, datetime, date, time, year, percent, string.
DPQL_FUNC_REGISTRY
DPQL_HIERARCHY
DPQL_HIERARCHY_DESCENDS_FROM
DPQL_HOUR
DPQL_JSON_EXTRACT
DPQL_LINK(value, link[, params..]) Links to value using the URL provided in link. Placeholders in link are replaced by the additional params. Placeholder values should be represented in sprintf format.
DPQL_MATRIX(group X, group Y) If both provided groups are non-null, creates a matrix table from them. Otherwise, it creates a standard grouped table.
DPQL_MINUTE
DPQL_MONTH
DPQL_MONTHNAME
DPQL_NOW() Gets the current date and time in the time zone of the agent running the query.
DPQL_OBJ_LANG
DPQL_PERCENT(condition[, decimals]) Determines percentage of total rows or rows withinthe current group which match the condition. The results are displayed as a percentage with decimals controlling the precision. 2 decimals are shown by default.
DPQL_PERCENT(sql, printed) Gets the sql but ensures that printed is used if the value is ever going to be displayed. This is mostly helpful in the GROUP BY clause where you needto group on one expression but display the results of another.
DPQL_PRINTABLE
DPQL_SQL_PASS
DPQL_STACK_GROUP
DPQL_TIME_LENGTH
DPQL_TO_UTC(date) Converts date to UTC from the current agent’s time zone.
DPQL_TOTAL
DPQL_UTC(expression) Ensures that all dates and times within this function are calculated using UTC. This can increase performance.
DPQL_Y
DPQL_X
DPQL_YEAR Extracts the year from a date/time valueas a 4 digit integer

A large number of functions are also available that have the exact same behavior as their MYSQL equivalents.

Published: 23/03/2018

Last updated: 10/05/2019