Effecient time ranges in 9.4/9.5?

From: Steven Lembark <lembark(at)wrkhors(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Cc: lembark(at)wrkhors(dot)com
Subject: Effecient time ranges in 9.4/9.5?
Date: 2015-10-02 13:26:12
Message-ID: 20151002082612.61886cbb.lembark@wrkhors.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)wrkhors(dot)com +1 888 359 3508

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-10-02 14:06:55 Re: Effecient time ranges in 9.4/9.5?
Previous Message Joseph Kregloh 2015-10-02 01:04:34 Re: Postgresql 9.4 and ZFS?