Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- {
- 1: [{start:0, finish: 12}],
- 2: [{start:0, finish: 12}, {start:12, finish:17}],
- 3: [{start:12, finish: 17}],
- 4: [{start:0, finish: 12}],
- 5: [{start:12, finish:17}, {start:17, finish: 0}],
- 6: [{start:0, finish: 12}, {start:12, finish:17}],
- 7: [{start:17, finish: 0}]
- }
- SELECT * FROM users WHERE
- timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
- timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
- ......
- timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}
- CREATE OR REPLACE FUNCTION weekly_activity(timetable jsonb) RETURNS integer AS $$
- BEGIN
- IF timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} THEN
- RETURN 111;
- END IF;
- END;
- $$ LANGUAGE plpgsql IMMUTABLE;
- CREATE INDEX CONCURRENTLY idx_weekly_activity ON table BTREE( weekly_activity(timetable))
- SELECT * FROM table WHERE weekly_activity(timetable) = 111;
- CREATE TYPE dow AS ENUM (
- 'Monday', 'Tuesday', 'Wednesday', 'Thursday',
- 'Friday', 'Saturday', 'Sunday'
- );
- 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
- );
- CREATE TABLE available (
- user_id int, -- REFERENCES users,
- dow dow,
- trange timerange,
- PRIMARY KEY (user_id, dow)
- );
- CREATE TABLE users(user_id) AS VALUES (1::int);
- INSERT INTO available VALUES
- ( 1, 'Monday', '[13:42,16:12]' );
- SELECT *
- FROM users AS u
- JOIN available AS a USING (user_id)
- WHERE dow = 'Monday' AND a.trange @> '[14:00,15:00]';
Add Comment
Please, Sign In to add comment