我正在尝试生成一系列时间戳,计算每个时间戳的一周一天,并仅选择周一至周六的时间戳,使用PostgreSQL。
。这是我到目前为止的尝试:
WITH
candidates AS (SELECT * FROM generate_series(
date_trunc('hour', CURRENT_TIMESTAMP - INTERVAL '2 hours'),
date_trunc('hour', CURRENT_TIMESTAMP - INTERVAL '2 hours')+INTERVAL '1 week',
INTERVAL '1 hour')),
candidows AS (SELECT EXTRACT(DOW FROM generate_series) FROM candidates),
candidatable AS (SELECT b.date_part AS dow, a.generate_series as start_booking FROM candidates a, candidows b)
SELECT * FROM candidatable WHERE dow != 0;
返回的视图包含一个陶列列,该列的行为是我所期望的,但是start_booking列只是重复相同的值:
dow | start_booking
-----+------------------------
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
2 | 2017-09-19 11:00:00+01
3 | 2017-09-19 11:00:00+01
3 | 2017-09-19 11:00:00+01
3 | 2017-09-19 11:00:00+01
如果我将最后一个选择语句替换为:
SELECT * FROM candidates;
然后,我试图将上述启动图视图查看的数据是顺序的,正如我所期望的:
generate_series
------------------------
2017-09-19 11:00:00+01
2017-09-19 12:00:00+01
2017-09-19 13:00:00+01
2017-09-19 14:00:00+01
2017-09-19 15:00:00+01
2017-09-19 16:00:00+01
我该怎么做才能从candidates.generate_series出现为candidatables.start_booking?
一个疯狂的猜测 - 您是否想:
WITH
candidates AS (SELECT generate_series start_booking, EXTRACT(DOW FROM generate_series) dow FROM generate_series(
date_trunc('hour', CURRENT_TIMESTAMP - INTERVAL '2 hours'),
date_trunc('hour', CURRENT_TIMESTAMP - INTERVAL '2 hours')+INTERVAL '1 week',
INTERVAL '1 hour'))
SELECT * FROM candidates WHERE dow != 0;
?..