Postgres Custom Range Type

Question

Question:

How to make a custom range type using time (or time with tz) as a base? What I have so far:

create time timerange as range (
    subtype = time,
    subtype_diff = ??? )

I think subtype_diff needs a function. For time types in pg, the minus function (difference) should work, but I can't seem to find the documentation that describes the correct syntax.

Background:

I am trying to make a scheduling app, where a service supplier would be able to show their availability and fees for different times of day, and a customer could see the price and book in real-time. The service supplier needs to be able to set different prices for different days or times of day. For example, a plumber might want, for a one hour visit:

$100 monday 0900-1800
$200 monday 1800-2200
$500 monday 2200-0000

To support this, the solution I am working on is as follows (any thoughts on better ways of doing this gratefully received)

I want to make a table that contains 'fee_rules'. I want to be able to lookup a given date, time and duration, and be able to check the associated fee based on a set of fee rules based on ranges. My proposed table schema:

id sequence
day_of_week integer [where 0 = Sunday, 1 = Monday..]
time_range [I want to make a custom time-range using only
hours:minutes of the day]
fee integer
fee_schedule_id (foreign key) (reference to a specific supplier, who is the 'owner' of that specific fee rule)

An example of a fee rule would be as follows:

id     day_of_week    time_range     fee   fee_schedule_id

12     01              10:00-18:00   100   543

For a given date, I plan to calculate day_of_week (e.g. day_of_week=01 for 'Monday') and generate a time_range based on the start_time and duration of the proposed visit e.g. visit_range=10:00-11:00. I want to be able to search using postgresql's range operators, e.g.

  select fee where day_of_week = '01' and visit_range <@ (range is contained by)time_range and fee_schedule_id = 543 [reference to the specific supplier's fees]

I don't think you need the subtype_diff: sqlfiddle.com/#!15/cc2ef/1 — user330315
– user330315, Commented Jan 19, 2015 at 6:53
subtype_diff only helps PostgreSQL to build better GiST (and SP-GiST) indexes, you may not need at all. postgresql.org/docs/current/static/… -- if you really need it, it must be a function, which must take two values of the subtype type as argument, and return a double precision value representing the difference between the two given values. postgresql.org/docs/current/static/sql-createtype.html — pozs
– pozs, Commented Jan 19, 2015 at 8:51
Thanks to @a_horse_with_no_name, Would you care to put your responses as an answer? otherwise I can do it and mark it as community wiki? thanks — ghufran syed
– ghufran syed, Commented Jan 20, 2015 at 8:22

ghufran syed · Accepted Answer · 2015-01-25 01:00:31Z

7

per @a_horse_with_no_name and @pozs "I don't think you need the subtype_diff":

create type timerange as range (subtype = time);

create table schedule 
(
  id integer not null primary key,
  time_range timerange
);

insert into schedule 
values
(1, timerange(time '08:00', time '10:00', '[]')),
(2, timerange(time '10:00', time '12:00', '[]'));

select *
from schedule
where time_range @> time '09:00'

answered Jan 25, 2015 at 1:00

community wiki

ghufran syed

Sign up to request clarification or add additional context in comments.

Comments

pba · Accepted Answer · 2023-02-23 16:52:56Z

0

subtype_diff of timerange is actually in an example of Postgres doc (since 9.5 version):

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

answered Feb 23, 2023 at 16:52

pba

8509 silver badges20 bronze badges

Collectives™ on Stack Overflow

Postgres Custom Range Type

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related