CODEWITHSUNDEEP
postgresql
vinnylinux
vinnylinux

Reputation: 7034

Modelling blackout time ranges on a week?

I'm trying to find an efficient way to store information about blackout time ranges over the course of a week. I'm storing everything like this today:

create table access_restriction (
  id serial primary key,
  user integer references user(id),
  weekday integer not null, # ISO-8601 week day
  start time not null,
  end time not null,
);

So, whenever I have to check if the user is allowed or not to do something, I select everything from there and do the logic on the application code. Is there a better way of doing with with PostgreSQL?

Upvotes: 0

Views: 44

Answers (1)

Glenn
Glenn

Reputation: 9170

With your existing structure, you could place the logic in the sql:

SELECT CASE WHEN COUNT(1) > 0 THEN true ELSE false END AS is_blacked_out
  FROM access_restriction a
  WHERE usr = :input_user_id
    AND weekday = :input_weekday
    AND start_time = (SELECT MAX(start_time)
                        FROM access_restriction
                        WHERE usr = a.usr
                          AND weekday = a.weekday
                          AND start_time <= :time_to_check
                     )
    AND end_time >= :time_to_check

with the three input parameters:

  • input_user_id
  • input_weekday
  • time_to_check

You could change that into a stored function. You would need to check things like boundary conditions (ex is end_time inclusive or not?) and assumptions such as no overlapping boundaries configured for a user.

For other data structures, you could imagine a bitmap representing the times in a week and a function which converts the input time to a bitmap to be "anded" with the user's blackout bitmap. But that might be overkill and it would really depend on things like the number of users, the granularity of the blackout period, the frequency of change of the blackout times, etc.

Upvotes: 1

Related Questions