With postgres, tstzrange data type is a very powerful format for querying data based on date ranges. ArcGIS query layers should support this type.
With date ranges, you can easily and quickly ask the following questions:
"is the date in the range?"
"does this date range overlap the other date range?"
So instead of all of those "if then" checking bounds you can take the date range the request requests, and do a simple overlaps test.
With range types you can do things like enforce a constraint (if you wanted to) that says no two ranges can overlap when someone inserts a new row. For example if you were making a calendar system that you didn't want anyone to double book themselves.
They have been around for a long time, but it is hard to get people to actually use them. Once they do, you never want to go back to having two different columns and then trying to figure out tests and if/then/else logic to figure out if you have overlapping values or whether you are inside, greater than, or outside the bounds.
You can check if a date range is completely inside another daterange, completely outside it, partially overlaps, etc...
