List of functions

DPQL Reference
Function Description
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_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_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_NOW() Gets the current date and time in the time zone of the agent running the query.
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_TO_UTC(date) Converts date to UTC from the current agent’s time zone.
DPQL_UTC(expression) Ensures that all dates and times within this function are calculated using UTC. This can increase performance.
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