DATETIME_DIFF

The DATETIME_DIFF function calculates the difference between two timestamps or dates given by start and end. The resulting number is the difference between the two specified timestamps/dates based on the granularity. The function can be used, for example, to calculate the duration of your business processes.

Syntax

SELECT DATETIME_DIFF(start, end)
SELECT DATETIME_DIFF(start, end, granularity)

Time attribute start and date can have the following formats:

Timestamp: {label/date.day}
Date and time macro: THIS(DAY)
Strings: "2022"

String formats

The strings must be in the following formats:

"YYYY-MM-DD HH24:MI" for minutes, "YYYY-MM-DD HH24" for hours, "YYYY-MM-DD" for days, "YYYY-MM" for months, "YYYY-WW" for weeks, and "YYYY" for years.
Use max one string as time attribute.
Strings must have the same granularity as the second time attribute.

Granularity is optional and can be one of the following:

MINUTE
HOUR
DAY
WEEK
MONTH
YEAR

Note

If the time attribute is in the date format, you cannot use the MINUTE or HOUR granularity.
If you do not specify the granularity, the greater granularity of the two date attributes is used.
If you specify the granularity, the two attributes must have the same or lower granularity. For example none of the attributes can have a MINUTE granularity if you specify HOUR as the parameter.

The DATETIME_DIFF function is not an aggregation function. Metrics built using DATETIME_DIFF function shall apply aggregation in its arguments (start and end) or aggregation needs to be applied on top of DATETIME_DIFF calculation. Both approaches may be combined.

Applying DATE_DIFF function

Date and time attributes in a logical data model are defined within a virtual Date dataset, different from the dataset in which facts and attributes from the same source table are mapped to. Date columns in source tables are technically mapped to references to virtual Date datasets. Since a Date dataset can be referenced by multiple fact or dimension datasets, you need to specify the source dataset of datetime data for calculation of DATETIME_DIFF. You need to do this even if there is currently only a single reference to the Date dataset in your model.

Depending on whether you want to first calculate the differences and then aggregate the results or first aggregate the dates and then calculate the difference, you specify the source dataset either with the BY keyword, or within the date aggregation function. Both ways are described in following examples:

Example 1 - DATETIME_DIFF is applied prior to aggregation

If you need to perform a DATETIME_DIFF calculation to two datetime columns within the same table, you should use the BY {dataset} after the DATETIME_DIFF function. Use an identifier of the dataset which is mapped to your table with the date columns. For example to calculate total duration of project(s) based on “start_date” and “end_date” dimension datasets referenced from “project” dataset, you can use following MAQL formula:

SELECT SUM(SELECT DATETIME_DIFF({label/start_date.day}, {label/end_date.day}) BY {dataset/project})

DATETIME_DIFF function in this example calculates the difference between start_date and end_date for each row of the dataset “project” and returns the result in days as a unit. This result is then aggregated using SUM. Such a metric can therefore return the number of days per any attribute of “project” dataset (e.g. per “Project”) as well as total number of days summed-up for all the projects.

Example 2 - DATETIME_DIFF is applied prior to aggregation and uses a time macro

If you need to perform a DATETIME_DIFF calculation to a datetime column in your table and a current datetime (i.e. calculate how long ago from now something happened), the approach is similar to the previous example, just instead of the second datetime use the time macro i.e. THIS(DAY).

You should use the BY {dataset} with an identifier of the dataset where your date column is mapped to. For example to calculate how long ago your project(s) ended based on “end_date” dimension dataset referenced from “project” dataset, you can use following MAQL formula:

SELECT AVG(SELECT DATETIME_DIFF({label/end_date.day}, THIS(DAY)) BY {dataset/project})

DATETIME_DIFF function in this example calculates the difference between end_date on each row of the dataset “project” and the current date and returns the result in days as a unit. This result is then aggregated using AVG. Such a metric can therefore return the result per any attribute of “project” dataset (e.g. per “Project”) as well as the overall result for all the projects.

Example 3 - DATETIME_DIFF is applied on top of aggregation

If you need to calculate duration of project(s) based on minimum and maximum date of activity (“activity_date” dimension dataset referenced from “activities” dataset), you can use following MAQL formula:

SELECT DATETIME_DIFF(MIN({label/activity_date.day},{dataset/activities}),MAX({label/activity_date.day},{dataset/activities}))

Minimum and maximum activity date is calculated first (based on the granularity of the insight the metric is used in) and then difference is calculated.

DATETIME_ADD