SearchKit/FormBuilder: Date/Time filtering suggestion

Overview

Provide more ways to filter dates and times.

Example use-case

Suppose we have a set of events and want users to be able to search for those that match their availability.

The user is available on Mondays and Wednesdays, and can't do before 10:30am or after 3pm

So, we want to be able to filter by the day of the week and by the time of day.

The example is for event start and end dates but it could be any datetime field.

Current behaviour

You can add the event start date as a field and use a smarty rewrite to show the day, but that field is not available as a filter.

You can add the event start date as a field and use the transformation functions for 'time only'. This can be added as a filter, but it is presented as a text match which kinda works (substring match on the time field) but does not allow comparisons.

Proposed behaviour

Add a day name transformation using the SQL DAYNAME() function and make that available as a filter. The filter should allow multiples (eg Monday or Wednesday).

However, that may not give much flexibility about how the name is displayed (I think it's just 'Tuesday' with no option for 'Tues') and might hit locale issues so maybe use DAYOFWEEK() command which returns values 1 to 7 and provide a way for the form designer to map those to day names. (Does an option group help?).

Probably makes sense to also add a month filter using MONTHNAME() (name) or MONTH (1-12)

For the 'time only' transformation, make that a time rather than text and allow >= <= comparisons so we can filter eg for start_time >= 10:30 and end_time <= 15:30

Comments

Anything else you would like the reviewer to note.