Обсуждение: Effecient time ranges in 9.4/9.5?
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
Steven Lembark <lembark@wrkhors.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
On Fri, Oct 02, 2015 at 10:06:55AM -0400, Tom Lane wrote:
> 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?
You'd
check whether both TZs are equal,
if so
ignore them
else
convert both operands to UTC,
do "time - time",
return result AT TIME ZONE UTC,
document that UTC is returned,
such that the user can know to apply "AT TIME ZONE ..."
to the result as needed.
That's not to say anything against the validity of your
other points :-)
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 10/4/15 6:18 AM, Karsten Hilbert wrote: > check whether both TZs are equal, > if so > ignore them > else > convert both operands to UTC, > do "time - time", > return result AT TIME ZONE UTC, > document that UTC is returned, > > such that the user can know to apply "AT TIME ZONE ..." > to the result as needed. The problem there is you can't reliably convert a time to a different timezone without knowing what date you're talking about. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Mon, Oct 12, 2015 at 12:14:10PM -0500, Jim Nasby wrote: > On 10/4/15 6:18 AM, Karsten Hilbert wrote: > > check whether both TZs are equal, > > if so > > ignore them > > else > > convert both operands to UTC, > > do "time - time", > > return result AT TIME ZONE UTC, > > document that UTC is returned, > > > >such that the user can know to apply "AT TIME ZONE ..." > >to the result as needed. > > The problem there is you can't reliably convert a time to a different > timezone without knowing what date you're talking about. I didn't realize we were talking time only. My reading of the thread suggested we were handling timestamps. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346