[CEP] Case Search Query support for creating calendar based dates

[snopoke]

Abstract

Define Case Search Query functions to enable the creation of calendar date ranges such as "last month" or "this week" (Monday - today).

Inspired by #31325 (comment)

Motivation

With the current functions it is possible to do basic manipulation of dates by adding or subtracting deltas from the the current date or a fixed date.

For example:

One month ago

date_add(today(), "months", -1)

However many use cases in reporting require calendar ranges with dates such as "the beginning of the month".

# Specification

Add a new function to the Case Search Query Language that allows manipulation of dates by adjusting parts of the date.

# set the date to the last day of the current month
adjust_date(today(), "month_end")

# set the date to the current day in January of this year
adjust_date(today(), "set_month", 1)

The function will take three arguments:

1. A date
   • This can either be a static value (any value accepted by the 'date' function) or it can be an expression that evaluates to a date such as today()
2. An operation name
   • See table below
3. An optional value
   • Operations such as month_start do not require a value

Proposed operations

Operation	Accepted Values	Description
set_day	1-31	Set the day of the month. Values outside of the range of the dates month will be considered to be the last day of the month.
set_month	1-12	Set the month of the year.
set_year	Year	Sets the year.
set_weekday	1-7 monday ... sunday	Sets the weekday of the dates current week. Accepts either integer weekdays from 1-7 or day names.
month_start		Sets the date to the first day of the dates current month.
month_end		Sets the date to the last day of the dates current month.
year_start		Sets the date to the first day of the dates current year.
year_end		Sets the date to the last day of the dates current year.

Example usage

Filter cases that were opened in the previous calendar month:

opened_on >= adjust_date(date_add(today(), "month", -1), "month_start")
and opened_on <= adjust_date(date_add(today(), "month", -1), "month_end")

Impact on users

Allow users to create queries with dynamic dates.

Impact on hosting

NA

Backwards compatibility

Since this is a new function there are no comparability issues

Release Timeline

NA

Open questions and issues

• Should the function accept multiple operations:

  # first day of the 2nd quarter
  adjust_date(today(), "set_month", 4, "month_start")
  

[esoergel]

This is awesome! I'd definitely run it by some target users if you get the chance, too - I bet they'd be excited about it.

Should the function accept multiple operations

It could, but you could accomplish the same with nested calls to adjust_date, which would also make the order the operations are applied more explicit. I'd be interested in what app builders say about that, though.

[millerdev]

Very nice!

One caveat (from the example in the CEP) that might be worth documenting is
date_add(adjust_date(today(), "month_end"), "months", -1) does not always yield a month end date.

Example:

date_add(adjust_date("2020-02-02", "month_end"), "months", -1)
result: '2020-01-29'

To get that you need to apply another month end adjustment:

adjust_date(date_add(adjust_date("2020-02-02", "month_end"), "months", -1), "month_end")
result: '2020-01-31'

Edit: or alternately do the month end adjustment last:

adjust_date(date_add("2020-02-02", "months", -1), "month_end")
result: '2020-01-31'

Should the function accept multiple operations

No. Nested calls is a better way to achieve that.

[snopoke]

To get that you need to apply another month end adjustment

Thanks for pointing that out, I originally had the order of the functions swapped as in your alternate example and only switched them because I thought it was easier to read (without thinking about the result).

Should the function accept multiple operations

Thanks for the feedback @esoergel and @millerdev. Using nesting was my intuition as well.

[millerdev]

Suggested update to the example in the CEP: "cases that were opened in the previous calendar month"

opened_on >= adjust_date(date_add(today(), "month", -1), "month_start")
and opened_on < adjust_date(today(), "month_start")

I think the original second comparison opened_on < adjust_date(date_add(today(), "month", -1), "month_end") would exclude cases opened on the last day of the previous month.