Proposal: SearchKit > Enable use of Extras fields (current date) in Field Transformations for Date Comparisons to Today
Overview
In SearchKit users/developers should be able to use the UI to add a column that does a datediff between today and a selected date field using the Field Transformation "Days between two dates".
Example use-case
- Create a new SearchKit query (saved search)
- Add a column: Extras > Current Date
- Open Field Transformations
- Select "Days between two dates" before the column "Current Date"
- On the right of that column, select from the dropdown the desired date field to compare to today.
Current behaviour
When creating queries in SearchKit, there is currently no easy way in the UI to do a datediff between the current date (CURDATE()) and a date field stored in the database.
Current workaround
We can hack our way to doing this by manually editing the SELECT clause of the query API call like this:
"DATEDIFF(CURDATE(), birth_date) AS DATEDIFF_today_birth_date",
You can do this directly in the "api_params" field of the saved search. Or you can export the saved search, edit the API call, and then import the saved search using the SearchKit Import UI.
Comments
Verified this behaviour on Civi 5.65.0
See: StackExchange Thread: How to calculate number of days to/from a date