I have a table which represents time ranges.
Column | Type | Modifiers
-------------+-----------------------------+--------------------------------------------------------------------------
id | bigint | not null default nextval('exploded_recurring_sessions_id_seq'::regclass)
schedule_id | bigint |
start_time | time without time zone |
end_time | time without time zone |
I'll then do a query such as this:
select from my_table where localtime >= start_time and localtime < end_time;
The business logic here makes sense in the case where end_time is midnight, because I consider a range to be inclusive of start_time and not inclusive of end_time.
Of course, this query doesn't actually work, because localtime will never been < end_time when end_time is 00:00:00.
There are two solutions to this I can think of.
- instead of
00:00:00, use'00:00:00'::time without time zone - interval '1 microsecond'for midnight. The likelihood of this every causing an incorrect calculation is probably low enough to never happen. And even if it does happen a handful of times per year, the consequences are essentially nonexistent, at least as my system now stands. However, it's an ugly solution, and using wrong data like this might have other consequences down the line. select from my_table where localtime >= start_time and (localtime < end_time or end_time == '00:00:00'). This seems pretty straightforward, but it makes it necessary to make one or more special indexes. Not a problem if it's the only solution, but feels like a drawback worth avoiding if possible.
Is there any other way to go about this? Some sort of special datetime function which accommodates this calculation? A better way to represent the data?
