代码之家 › 专栏 › 技术社区 › ere

使用postgres generate\u series生成定期计划

generate-series postgresql-9.4 postgresql

ere · 技术社区 · 7 年前

我有一张有重复日期的表,叫做 Events ,如何获取此表并根据事件的wday和time从系列中生成特定的即将到来的日期?(例如,只有wday“周一”和开始时间“7pm”才重要)

事件

+-----+---------------------------+---------------------+
| id  | start_at                  | recurring_schedule  |
+-----+---------------------------+---------------------+
| 358 | 2015-01-23 20:00:00 +0000 | Weekly              |
| 359 | 2016-01-22 19:30:00 +1100 | Monthly             |
| 360 | 2016-02-01 19:00:00 +1100 | Weekly              |
| 361 | 2016-02-01 20:00:00 +0000 | Weekly              |
| 362 | 2014-02-13 20:00:00 +0000 | Bi-Weekly           |
+-----+---------------------------+---------------------+

start\u at(日期时间,用于道琼斯指数和开始时间,忽略实际日期)

为简单起见,您可以忽略重复的日程安排,只需假设所有事件都是每周发生的,例如总是在一周的同一天。

如何将这样的表转换为这样的内容:

未来日志

+----------+---------------------------+
| event_id | start_at                  |
+----------+---------------------------+
| 35       | 2018-04-11 19:30:00 +0000 |
| 94       | 2018-04-12 20:00:00 +0100 |
| 269      | 2018-04-13 18:30:00 +0100 |
| 45       | 2018-04-13 20:00:00 +0100 |
| 242      | 2018-04-13 19:30:00 +1100 |    
| 35       | 2018-04-18 19:30:00 +0000 |
| 94       | 2018-04-19 20:00:00 +0100 |
| 269      | 2018-04-20 18:30:00 +0100 |
| 45       | 2018-04-20 20:00:00 +0100 |
| 242      | 2018-04-20 19:30:00 +1100 |
+----------+---------------------------+

我想 SELECT FROM events 理论事件,并使用generate\u系列之类的东西从每个事件创建6-8周的未来日期,以构建事件时间表。

1 回复 | 直到 7 年前

Haleemur Ali 7 年前

更新答案:

在case语句中使用generate\u series构建序列,类似于我在原始答案中所做的ohw,频率根据列的不同而不同 recurring_schedule 。

指定希望序列生成为绝对日期的日期 '2020-01-01'::timestamptz 正如我在下面使用的,of可以传递一个相对日期,例如。 NOW() + INTERVAL '10 weeks' 相反

SELECT id event_id, start_at, 
CASE recurring_schedule 
    WHEN 'Weekly' 
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 weeks'::INTERVAL)
    WHEN 'Bi-Weekly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '2 weeks'::INTERVAL)
    WHEN 'Monthly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 month'::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM events;

带有json字段的架构的原始答案:

datetime类型的generate\u series的语法为

generate_series(start_time, end_time, step_interval)

因为您的计划是JSON格式的,包含了时间间隔,所以您可以这样构造查询,并根据需要添加更多的计划类型。

WITH test (id, start_at, place_id, recurring_schedule) AS (

VALUES 
(358, '2015-01-23 20:00:00 +0000'::TIMESTAMPTZ, 412, 
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'::JSONB),

(359, '2016-01-22 19:30:00 +1100', 414,
'{"validations":{"day":[1]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(360, '2016-02-01 19:00:00 +1100', 415, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(361, '2016-02-01 20:00:00 +0000', 416, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(362, '2014-02-13 20:00:00 +0000', 417,
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}')
)
SELECT id, start_at, place_id, 
CASE recurring_schedule->>'rule_type' 
    WHEN 'IceCube::WeeklyRule' 
        THEN GENERATE_SERIES(start_at, NOW(), (recurring_schedule->>'interval' || ' WEEK')::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM test;