Steven Lembark <lembark@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
