Recently I was assigned to create a filter in an API that give you for a specific time span only events that occured at either AM or PM (by passing a timezone). Luckily in our database except from the epoch time we also had a Date type field and a Time type field.
The Time type is special, as it is designed to store not specifically and only the time of the day but also elapsed time during two events.
In the later case time can take values from -838:59:59 to 838:59:59.
Also its not valid to do a search from 23:00:00 to 01:00:00, but rather need to do from
23:00:00 to 23:59:59 and
00:00:00 to 01:00:00
so depending on the timezone we had to project, the time zone AM and PM zones to UTC and split accordingly.
So I ended creating the follow graph with a colleague, which made things really easy and I thought it would be a nice idea to share it.