Guest User

Untitled

a guest
Mar 17th, 2018
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.85 KB | None | 0 0
  1. {
  2. 1: [{start:0, finish: 12}],
  3. 2: [{start:0, finish: 12}, {start:12, finish:17}],
  4. 3: [{start:12, finish: 17}],
  5. 4: [{start:0, finish: 12}],
  6. 5: [{start:12, finish:17}, {start:17, finish: 0}],
  7. 6: [{start:0, finish: 12}, {start:12, finish:17}],
  8. 7: [{start:17, finish: 0}]
  9. }
  10.  
  11. SELECT * FROM users WHERE
  12. timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
  13. timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
  14. ......
  15. timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}
  16.  
  17. CREATE OR REPLACE FUNCTION weekly_activity(timetable jsonb) RETURNS integer AS $$
  18. BEGIN
  19. IF timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} THEN
  20. RETURN 111;
  21. END IF;
  22. END;
  23. $$ LANGUAGE plpgsql IMMUTABLE;
  24.  
  25. CREATE INDEX CONCURRENTLY idx_weekly_activity ON table BTREE( weekly_activity(timetable))
  26.  
  27. SELECT * FROM table WHERE weekly_activity(timetable) = 111;
  28.  
  29. CREATE TYPE dow AS ENUM (
  30. 'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  31. 'Friday', 'Saturday', 'Sunday'
  32. );
  33.  
  34. CREATE FUNCTION time_subtype_diff(x time, y time)
  35. RETURNS float8 AS $$
  36. SELECT EXTRACT(EPOCH FROM (x - y))
  37. $$ LANGUAGE sql
  38. STRICT IMMUTABLE;
  39.  
  40. CREATE TYPE timerange AS RANGE (
  41. subtype = time,
  42. subtype_diff = time_subtype_diff
  43. );
  44.  
  45. CREATE TABLE available (
  46. user_id int, -- REFERENCES users,
  47. dow dow,
  48. trange timerange,
  49. PRIMARY KEY (user_id, dow)
  50. );
  51.  
  52. CREATE TABLE users(user_id) AS VALUES (1::int);
  53.  
  54. INSERT INTO available VALUES
  55. ( 1, 'Monday', '[13:42,16:12]' );
  56.  
  57. SELECT *
  58. FROM users AS u
  59. JOIN available AS a USING (user_id)
  60. WHERE dow = 'Monday' AND a.trange @> '[14:00,15:00]';
Add Comment
Please, Sign In to add comment