在一周中的某一天表演

Question

希望有人能在这里提供一些建议，我正在使用一个PostgreSQL数据库。

此查询的目的是确定当前允许访问的用户。目标是提供一个日期和时间范围作为输入，并查看可以在指定日期和时间范围内允许访问的用户帐户。

该表是这样设置的，时间列用于“开始时间”和“结束时间”，这两个列指定了用户允许的时间范围。然后是一周中的每一天的布尔列，该列指定是否允许该用户访问当天的时间范围。

   [START TIME]    [END TIME]   [MON]   [TUES]     [WED]     [THURS]     [FRI]    [SAT]     [SUN]
    09:00:00       11:00:00     True     True      True       True       True     False     False 

现在，这看起来很简单，但在我看来，系统首先需要知道一周中的哪一天，然后使用一个很长的“大小写”来表示如果输入的日期是一个星期一，对于这个用户来说，table.mon = true，那么这个用户就满足了这个标准。

到目前为止，我有这样的事情：

DO $$
DECLARE 

--Specify 'variables'
active_date timestamp := '2020-10-4';
start_time time := '00:00:00';
end_time time := '23:59:00';
day_of_week text := to_char(active_date, 'day');

BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct
    date(account.lastupdated) as "Date",
    concat(to_char(account.start_time, 'HH:MI'), ' - ', to_char(account.end_time, 'HH:MI')) as "Time Range"
    from account
    where account.lastupdated >= active_date AND account.lastupdated < active_date + interval '1 day'
    and account.start_time >= start_time AND account.end_time <= end_time;
END $$;

SELECT * FROM temp_output;

我坚持的是，只有在输入的日期对于返回的每一行都有一个布尔值"True“时，输出才会显示值。

类似于：

case when day_of_week = 'sunday' and account.sun = "True" then ...
     when day_of_week = 'monday' and account.mon = "True" then...
     when day_of_week = 'tuesday' and account.tues = "True" then...

但是，我如何根据输入的日期来实现整个结果集的逻辑呢？

Answer 1

如果只需确定某一行是否与某一天匹配，则请检查适当的列是否为真。

-- Check if this is valid for Monday
where mon

正如您正在发现的那样，使用单个列来存储列表项在SQL中效果不佳。

在传统的SQL中，您将有一个连接表来存储这次应用的日期。

create table schedule (
  id bigserial primary key,
  start_time time not null,
  end_time time not null
);

create table schedule_days (
  schedule_id bigint not null references schedule(id),
  day text not null
);

insert into schedule (start_time, end_time) values ('09:00', '11:00');
insert into schedule_days (schedule_id, day) values
  (1, 'Mon'), (1, 'Tues'), (1, 'Wed'), (1, 'Thurs'), (1, 'Fri');

select *
from schedule s
join schedule_days sd on s.id = sd.schedule_id
where current_time between start_time and end_time
  and day = $1

我们可以通过使用枚举来存储天数来改进这一点。这确保只添加正确的值。它节省存储空间，因为它们实际上是作为整数存储的。它们可以作为字符串继续被查询。

create type day_of_week as ('Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat', 'Sun');

create table schedule_days (
  schedule_id bigint not null references schedule(id),
  day day_of_week not null
);

或者，您可以在几天内使用单个阵列列。

create table schedule (
  id bigserial primary key,
  start_time time not null,
  end_time time not null,
  days day_of_week[] not null
);

insert into schedule (start_time, end_time, days)
  values ('09:00', '11:00', array['Mon', 'Tues', 'Wed', 'Thurs', 'Fri']);

然后，在标准化输入之后，检查它是否包含在days数组中。

select *
from schedule s
where current_time between start_time and end_time
  and days @> array['Tues']