Searching records by a time range

Hello all! I have a table on a web page containing events, with a single date/time field indicating the start date and time.
I would like to build a filter to show only events in the morning, afternoon and evening.
I have two questions:

  • what page element can I use for the user to pick morning/afternoon/evening? I’d like it to be easy so I don’t want to use a date/time picker
  • how do I build the search?
    Many thanks for anyone who can provide some help! :grinning:

There are probably a few ways you can do this, but off the top of my head here’s two ways of going about it…

1 - Probably the simplest, and the fastest in terms of performance, would be simply to have a field on your event datatype that specifies which time (morning, afternoon, evening) the event is scheduled for.

To do that, I’d have an option set containing the 3 time slots, and attributes for start and end times (which would be of type ‘date’, although the day, month and year would be set to an arbitrary date (such as 01/01/2000), as you’re only interested in the times). It might also be possible to use numbers here instead of date (for the minutes, hours, and seconds) for the same purpose.

Then, whenever a new event is created or modified, set the Timeslot field (morning, afternoon, or evening), based on the event’s time, to one of the options form the options set.

Then it’s super easy to filter the events on your page - just have a way of selecting the timeslot (i.e. a dropdown menu of your option set, or some selectable icons with a custom state etc.) then simply add a constraint on your search to return only events who’s timeslots match the selected timeslot.

The benefit of this is that it’s an easy search, so should be fast, and will only return the required events.

2 - another option, if you don’t have a field on the event other than the start time (and don’t want to create one), is to use an advanced filter on the returned results.

Assuming you’d use the same option set as outlined above, you could apply an advanced filter to show the returned events who’s start date/time, with the year, month, and date modified to match the arbitrary values in your option set, is greater than the selected timeslot’s start time AND less than the selected timeslot’s end time.

This way you don’t have to add any new fields to the data, BUT it is more complex, and advanced filters are applied AFTER the search, client side, so will be a lot slower, especially if you have a lot of data.

If you have other ways to filter the returned events, for example if you’re allowing a specific date range to be selected, then you can use that as a constraint on the search to reduce the amount of data being filtered by the advanced filter and improve performance.

As I said, there are probably other ways to do it too, but they’re the first two that came to mind.

EDIT - all of the above doesn’t take into account complications arising from timezones - so if your events are held in different timezones you’ll have to consider how, or even if, it makes any sense do display events based on time of day, and if so decide whether to show events base on the user’s timezone, or the event location timezone etc.

2 Likes

Dear Adam,
thank you so much for your reply. If I understand correctly, option 1 entails building a workflow triggered by the creation of a new record, and this workflow would put data in the timeslot field based on an evaluation of the time. This is quite clear to me if the time is in a separate record, but how do I evaluate the time only part of a field, when the date can be different (and not always the same as in the example you gave)? Am I forced to have separate fields for date and time?
ON your second option, would you be able to point me to any online resource where I can learn how to build an advanced filter?
Thanks again for your help

This topic was automatically closed after 70 days. New replies are no longer allowed.