postgresql - 如何获取商店的交易时间

Question

我希望得到一家商店的交易时间。我的模式代表了营业时间，以及商店营业的持续时间，这很琐碎。提供了一个覆盖表来存储与标准交易时间不同的日期，或者如果商店在给定的一天或几天关闭。例如：圣诞节，或周末等。

我怎样才能显示当前星期（周日至周六）的相关交易时间，并覆盖每一天的时间？

我尝试过的架构和一些 SQL 如下：

CREATE TABLE shop(
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL
);
INSERT INTO shop(id, name) VALUES (1, 'Empire');
INSERT INTO shop(id, name) VALUES (2, 'Dodgey Joes');

CREATE TABLE trading_hours(
        id SERIAL PRIMARY KEY,
        time_open TIME NOT NULL,
        duration INTEGER NOT NULL, --In minutes
        day_of_week INTEGER NOT NULL,
        shop_id INTEGER REFERENCES shop(id)
);
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (1, '6:00', 720, 0, 1); -- sun 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (2, '6:10', 720, 1, 1); -- mon 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (3, '6:20', 720, 2, 1); -- tue 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (4, '6:30', 480, 3, 1); -- wed 8 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (5, '6:00', 720, 4, 1); -- thur 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (6, '6:00', 240, 5, 1); -- fri 4 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (7, '23:00', 240, 5, 1);-- fri 4 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (8, '6:00', 1200, 6, 1); -- sat 20 hours

INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (9, '7:00', 720, 0, 2); -- sun 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (10, '7:10', 720, 1, 2); -- mon 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (11, '7:20', 480, 2, 2); -- tue 8 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (12, '7:30', 510, 3, 2); -- wed 8.5 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (13, '7:00', 720, 4, 2); -- thur 12 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (14, '7:00', 240, 5, 2); -- fri 4 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (15, '12:00', 360, 5, 2);-- fri 6 hours
INSERT INTO trading_hours(id, time_open, duration, day_of_week, shop_id) VALUES (16, '7:00', 1200, 6, 2); -- sat 20 hours
CREATE TABLE other_hours(
        id SERIAL PRIMARY KEY,
        time_open TIME NOT NULL,
        duration INTEGER NOT NULL, --In minutes
        day_of_week INTEGER NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        closed BOOLEAN NOT NULL,
        shop_id INTEGER REFERENCES shop(id)
);
-- shop 1 closed xmas day
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (1, '00:00', 0, 3, '2019-12-25', '2019-12-25', true, 1);
-- shop 1 closed every tuesday in January
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (2, '00:00', 0, 2, '2020-01-01', '2020-01-31', true, 1);

--shop 2 closed new years day
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (3, '00:00', 0, 3, '2020-01-01', '2020-01-01', true, 2);
--shop two closed Jan 10 to Feb 10
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (4, '00:00', 0, 0, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (5, '00:00', 0, 1, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (6, '00:00', 0, 2, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (7, '00:00', 0, 3, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (8, '00:00', 0, 4, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (9, '00:00', 0, 5, '2020-01-01', '2020-01-01', true, 2);
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (10, '00:00', 0, 6, '2020-01-01', '2020-01-01', true, 2);

-- shop 1 open 11:00am on Jan 2
INSERT INTO other_hours(id, time_open, duration, day_of_week, start_date, end_date, closed, shop_id) VALUES (11, '11:00', 420, 4, '2020-01-02', '2020-01-02', true, 1);

还有一些 SQL：

elect th.day_of_week,
    case th.day_of_week
    WHEN 0 THEN 'Sun'
    WHEN 1 THEN 'Mon'
    WHEN 2 THEN 'Tue'
    WHEN 3 THEN 'Wed'
    WHEN 4 THEN 'Thu'
    WHEN 5 THEN 'Fri'
    WHEN 6 THEN 'Sat'
end AS day_name,
sh.name, th.time_open,
th.time_open +(th.duration::text||' minute')::INTERVAL AS time_close,
oh.time_open AS other_time_open,
oh.time_open +(oh.duration::text||' minute')::INTERVAL AS other_time_close,
case
  WHEN DATE(NOW()) >= oh.start_date AND DATE(NOW()) <= oh.end_date
        THEN oh.time_open
        ELSE th.time_open
  END AS kev_open_time
FROM trading_hours th
JOIN shop sh ON th.shop_id=sh.id
LEFT JOIN other_hours oh ON th.shop_id=oh.shop_id AND oh.day_of_week=th.day_of_week
ORDER BY sh.name, th.day_of_week, th.time_open
;

标签： postgresql

Answer 1

正如我在评论中提到的，WHEN DATE(NOW()) >= oh.start_date AND DATE(NOW()) <= oh.end_date对我来说似乎是错误的。DATE(NOW())应该是一周中特定日期的日期。

我想我会从为本周的日子生成日期开始。就像是：

SELECT date, extract(dow from date) as day_of_week 
FROM (
  SELECT current_date + series AS date FROM generate_series(1,7) AS series
) AS "dates"

或者更好的是，将其与工作日对齐：

SELECT date, extract(dow from date) as day_of_week 
FROM (
   SELECT current_date - extract(dow from current_date)::Int + series AS date 
   FROM generate_series(0,6) AS series
) AS "dates"

这使：

    date    | day_of_week 
------------+-------------
 2019-12-22 |           0
 2019-12-23 |           1
 2019-12-24 |           2
 2019-12-25 |           3
 2019-12-26 |           4
 2019-12-27 |           5
 2019-12-28 |           6

现在您可以将它与您的查询结合使用，dates.date而不是DATE(NOW())：

SELECT 
  dates.date AS date,
  to_char(dates.date, 'day') AS day_of_week,
  sh.name, 
  COALESCE(oh.time_open, th.time_open) AS time_open,
  CASE
    WHEN oh.time_open IS NOT NULL
    THEN oh.time_open + (oh.duration::text||' minute')::INTERVAL
    ELSE th.time_open + (th.duration::text||' minute')::INTERVAL
  END AS time_close
FROM (
   SELECT current_date - extract(dow from current_date)::Int + series AS date 
   FROM generate_series(0,6) AS series
) AS dates
LEFT JOIN trading_hours th ON th.day_of_week = extract(dow from dates.date)
LEFT JOIN other_hours oh ON 
   th.shop_id=oh.shop_id AND 
   oh.day_of_week=th.day_of_week AND 
   dates.date >= oh.start_date AND 
   dates.date <= oh.end_date
JOIN shop sh ON th.shop_id=sh.id
ORDER BY sh.name, dates.date, time_open;