Sorting Time in a Repeating Group Taking Into Consideration Am/Pm


I’m creating a happy hour app and I would like to sort upcoming happy hours by the current time.

So, if it’s 1:00 pm any happy hours that are ongoing will be displayed first as “ongoing” and any happy hours that will start after 1:00 pm will say “upcoming”, and happy hours that have finished before 1:00 pm will be hidden.

When adding a happy hour to the database I fill out four time-related inputs for each happy hour.

  • opening_hour
  • am_or_pm
  • closing_hour
  • am_or_pm

The opening_hour and closing_hour are data types that are linked to an option set with the numbers 1:00 through 12:00 in 15-minute increments.

Screen Shot 2024-02-09 at 1.50.37 PM

Trying to display the data as I mentioned above is where I am stuck.

When a user adds their happy hour to the database a workflow copies the opening_hour input into a data field called sort_time which is a number field. Then, I sort the repeating group by the sort_time.

However, this is a problem because any happy hours that start before 12:00 pm noon are at the very bottom of the repeating group because the 11 in 11:00 am is a larger number than 1.

How can I sort my repeating group to also take into consideration the am or pm, and make sure that the am times go before the pm times?

I should be able to figure out the “ongoing” and “upcoming” conditionals afterward.

Thank you!

Add Order field of type number to option set, fill it appropriately and sort by that field.

Thanks so much for your response!

Would you mind sharing more information? I’m not sure how to do what you’ve suggested.

I have two option sets, one for hours of the day and one for am/pm

This is what the hours of the day option set looks like:

This is what the am/pm option set looks like
Screen Shot 2024-02-10 at 10.11.38 AM

The sort_time is a data field inside my Happy Hour data type. This is how I’m currently sorting by opening time, but it doesn’t take into consideration the am/pm option set.

This is what the Happy Hour data type looks like

Thank you for your help!

Then I’d suggest a different approach:

  1. In Happy Hour data type add open_time and close_time fields of type number
  2. In _Hours_Of_The_Day add field Offset of type number indicating number of minutes since 12:00, and fill them appropriately, so that 12:15 option would have it set to 15, 1:15 would be set to 75 and so on…
  3. In _Hours_AmPm add field Offset of type number and set it to 0 for am and 720 (12 * 60) for pm
  4. When creating/updating Happy Hour thing, set open_time to _Hours_Of_The_Day's Offset + _Hours_AmPm's Offset, same about close_time
  5. You can calculate hour of closing_hour using close_time / 60 :floor, and minutes of closing_hour using close_time <- modulo -> 60, and time in a date using date :rounded down to date :+minutes Happy Hours's close_time
  6. Now to correctly sort hours within a day, just use natural sorting by close_time or open_time.
  7. If you need to display happy hours for the whole week, you will probably add similar Offset field to _Day_Of_The_Week indicating number of minutes since Sunday midnight, but there you will most probably face leap year problem as well as locale specificity (not sure if this is important for your case). In this case you need to rethink the whole approach, as it does not suit locale-aware contexts (when, for example, week starts on Monday instead of Sunday).

You don’t need AM/PM, you just need to work with time as computers do, which is in 24 hour increments, and broken down into minutes and even further into milliseconds.

Data for time should just be a number field, between 0-23 for hours and have another field for minutes, between 0-59.

You can alter your option sets to include attributes such as these, but that will make sorting impossible since you can not sort a data type based on data fields related to option set values.

So, perhaps, on the data type, just have a single field of type number that represents a total number of minutes (12:15PM is equal to 12*60+15=735 minutes) then you can sort by that field from the data type, and can easily display values to users in format of 12:15PM by doing simple math from the field)