Re: Effecient time ranges in 9.4/9.5?

Steven Lembark <lembark(at)wrkhors(dot)com> writes:
> 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.

Why do you think it needs to be "timetzrange" and not "timerange"?
Most stores I know of close at, say, 5PM local time, not 5PM during
standard time and some other time during daylight-savings.

> The examples in [1] & [2] don't include a working subtype_diff (just a
> reference to "float8mi" without defining it).

That example works fine, as you'd soon find if you tried
copying-and-pasting it into psql. float8mi() is a built-in function,
namely the one underlying the "float8 - float8" operator. The example is
oversimplified a little bit, in that that subtraction operator yields
float8, which just happens to be the required result type of a
subtype_diff function. For most types, you'd need a subtraction operator
and then a conversion to float8, wrapped up as a single function.

The problem you'll have in defining timetzrange is that you first need to
invent a "timetz - timetz" operator, which doesn't exist as a builtin
function because the behavior seems not well-defined. What would you do
with the timezone fields?

If you went with "timerange" then the required subtraction operator
does already exist, and you just need a wrapper function to cast the
result to float8, probably with extract(epoch ...).

regards, tom lane