Trying to store open hours for storefront operations.
These are degenerate sets of
( store + weekday + open time + close time )
(i.e., candidate key == all fields). Ultimate goal is to compare hours
for service times (e.g., seating, pickup, delivery) to food prep times
(e.g., breakast or lunch menu).
I'd like to store them as:
( store + weekday + timerange )
to simplify exclusion constraints and joins for overlapping food prep
and service times. Lacking a built-in "timetzrange", I'm stuck defining
the type.
I think a working subtype_diff to effeciently support exclusion
constraints on ( store with =, + weekday with =, hours with &&).
In particular, a working subtype_diff, assuming that the hours are all
in the range of 0000 .. 2400 (i.e., no cross-day intervals).
The examples in [1] & [2] don't include a working subtype_diff (just a
reference to "float8mi" without defining it). At the least a working
time -> float8 operator might be nothing more than a cast but I don't
see how to do it offhand.
There are several cases I've found of people wanting to create a
working time range, without any specifics of how (e.g., [3]). I can
see where the built-in would have issues ([4], [5]) but using time
ranges with dates as templates to produce timestamp-ranges makes life
soooo much easier with scheduling.
The 9.4 doc's describe the subtype_diff as necessary for effective
gist indexing. Then again, the builtins for time may be sufficient
to just define subtype = timetz and be done with it... I cannot find
any references either way.
It's not that hard to handle differences mod-24hrs:
diff = ( upper - lower + 24 % 24 );
if upper < lower the +24 corrects the sign; if upper > lower the % 24
keeps the result in range. I'm just not entirely
Q: Is the subtype_diff really useful for indexing if the subtype is
timetz?
Q: If so, where is an example to an effecient diff for the times?
thanks
[1] <http://www.postgresql.org/docs/9.4/static/sql-createtype.html>
[2] <http://www.postgresql.org/docs/9.4/static/rangetypes.html>
[3] <http://stackoverflow.com/questions/28017891/postgres-custom-range-type>
[4] <http://grokbase.com/t/postgresql/pgsql-general/128355kvhc/range-types-in-9-2>
[5] <https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL>
--
Steven Lembark 3646 Flora Pl
Workhorse Computing St Louis, MO 63110
lembark@wrkhors.com +1 888 359 3508