>我有以下模式:
计划表示定期事件的计划。
# Table name: schedules
#
# id :integer not null, primary key
# start_date :date
# end_date :date
">天"表示发生活动的计划的"工作日"。
# Table name: days
#
# id :integer not null, primary key
# schedule_id :integer
# wday :integer
时隙表示活动可能发生的小时数(每天可能很多小时(。
# Table name: time_slots
#
# id :integer not null, primary key
# day_id :integer
# start_time :time not null
# end_time :time not null
示例数据集可以是:
- 1 时间表,开始日期为 6 月 1 日,结束日期为 6 月 30 日
- 1 天,宽度 wday = 0(活动发生在 6 月的每个星期一(
- 2 个时间段。 1 上午 8 点start_hour,上午 11 点end_hour。另一个下午 1 点和下午 3 点start_hour end_hour
鉴于上面的例子(在下面的SQL中表示(,我想为六月的每个星期一返回一条记录,包括它们的日历日期。
2017 年 6 月有 4 个星期一,因此上面的示例如下所示:
id | wday | calendar_date |
----+------+---------------+
1 | 2 | 2017-06-05 |
1 | 2 | 2017-06-12 |
1 | 2 | 2017-06-19 |
1 | 2 | 2017-06-26 |
谁能引导我走向正确的方向?
在下面设置 PSQL:
CREATE TABLE schedules (
id integer NOT NULL,
start_date date,
end_date date);
CREATE TABLE days (
id integer NOT NULL,
schedule_id integer,
wday integer);
CREATE TABLE time_slots (
id integer NOT NULL,
start_time time,
end_time time,
day_id integer);
INSERT INTO schedules (id, start_date, end_date) VALUES (1, '2017-06-01', '2017-06-30');
INSERT INTO days (id, schedule_id, wday) VALUES (1, 1, 0);
INSERT INTO time_slots (id, start_time, end_time, day_id) VALUES (1, '18:00', '19:00', 1);
select s.id, wday, start_date + g calendar_date
from schedules s
cross join generate_series(0, end_date - start_date) g
join days d on d.schedule_id = s.id
where extract(isodow from start_date + g) - 1 = wday
http://rextester.com/GTPQ53700
笔记:
- 使用
generate_series()
您可以生成数据库中没有数据的行 - 我假设您希望从星期一开始几周(因为它在您的表格中由
0
表示(。最接近这一点的是PostgreSQL中的ISODOW
,但周一至周日使用1-7。(另一方面,DOW
对星期日-星期一使用 0-6:因此周从星期日开始,DOW
。 这实际上不会研究
time_slots
.如果需要,请将以下谓词添加到WHERE
子句中:and exists(select 1 from time_slots t where t.day_id = d.id)