Date and time macros

Learn how to use macros to work with date and time in CQL.

Date and time macros are specialized functions written in natural language that allow you to perform operations on date and time values. They simplify common tasks like getting the current period or calculating the elapsed time between two dates.

This is the summary of the available date-time macros for quick reference:

MacroDescription
Current periodGets the current week, month, or year.
Elapsed timeCalculates the elapsed time between two date-time values.

Current period

This macro returns the current week, month, or year. It can be used to extract a temporal value representing the current period in various formats.

To determine the current period, this macro uses the time zone and locale configured at the application level. These settings define the daylight saving time rules and which days of the week are weekdays or weekend days.

Syntax

This macro has the following syntax:

this /*<period>*/
Try in Playground

The /*<period>*/ is a literal word that represents a specific date-time period and cannot be specified dynamically using expressions.

Here is a list of the supported period specifiers:

PeriodDescriptionExample
weekA temporal value representing the current week.2024-W20
monthA temporal value representing the current month2024-05
yearAn integer value representing the current year.2024

Examples

Here is an example of how to get the current week:

this week // 2024-W20
Try in Playground

You can also get the current month:

this month // 2024-05
Try in Playground

And the current year:

this year // 2024
Try in Playground

Elapsed time

This macro calculates the elapsed time between two date-time values in terms of a specific unit. It returns a whole number representing the total number of complete units between two dates. The result is positive if the start date is earlier than the end date and negative if it is the other way around.

For example, the number of months between June 15 and August 14 is one month because it is one day short of two complete months. In contrast, reversing the dates results in a negative interval of -1 month.

Croct's mascot neutral
Time travels, and so do your results!

Mixing instants and local date times can lead to unexpected results because of time zone adjustments involved in converting local date times to instants.

Syntax

This macro has the following syntax:

number of /*<time-unit>*/ between /*<start>*/ and /*<end>*/
Try in Playground

You can optionally prefix the macro with a definite article for readability:

the number of /*<time-unit>*/ between /*<start>*/ and /*<end>*/
Try in Playground

The /*<time-unit>*/ is a literal word that represent a specific unit of time, and cannot be specified dynamically using expressions.

Here is a list of the supported time-unit specifiers:

UnitNotes
daysThe number of days may differ from the number of 24-hour periods due to daylight saving time adjustments.
weeksA week is defined as a period of seven days.
weekdaysThe number of weekdays depends on the locale configured at the application level.
weekend daysThe number of weekend days depends on the locale configured at the application level.
monthsThe months can vary depending on the start and end dates, and may not necessarily align with 30-day periods.
yearsThe number of years may differ from the number of 365-day periods due to leap years.
hoursFor local date times, a day is defined as a period of 24 hours.
minutesA minute is defined as 60 seconds.
secondsA second is defined as 1000 milliseconds.
milliseconds millisA millisecond is defined as 1000 microseconds.
microseconds microsA microsecond is defined as 1000 nanoseconds.
nanoseconds nanosThe smallest supported unit of time.

Parameters

The following list describes the supported parameters:

startDate|DateTime

The start of the interval (inclusive). This parameter is automatically converted to an instant in the configured time zone if necessary.

endDate|DateTime

The end of the interval (exclusive). This parameter is automatically converted to an instant in the configured time zone if necessary.

Examples

Here is an example of how to calculate the number of days between two dates:

number of days between "2024-08-01" and "2024-08-15" // 14
Try in Playground

You can also calculate the amount of time between two date-time values in terms of months, weeks, or other units:

number of seconds between "2024-08-01T01:30" and "2024-08-01T02" // 1800
Try in Playground