生成具有重复日期的日期系列 [英] Generate date series with repeating dates

问题描述

如何生成每个日期重复 5 次的长日期序列，如下所示?

How to generate a long date series that repeats each date 5 times, like below?

1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/1/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
1/2/2018
etc.

推荐答案

Redshift 不是 Postgres.不支持generate_series().

Redshift is not Postgres. Does not support generate_series().

穷人的默认值是一个数字表(从 1 开始).每个数据库创建一次.就像这里演示的一样.

The poor man's default is a number table (starting with 1). Create it once per database. Like demonstrated here.

那么:

SELECT date '2018-1-1' + day.number - 1
FROM   number AS day
     , number AS repeat
WHERE  day.number    <= 10  -- number of days in date range
AND    repeat.number <= 5   -- number of repetitions
ORDER  BY day.number;

<小时>

在Postgres中，你可以直接使用generate_series():

SELECT day::date
FROM   generate_series(timestamp '2018-1-1'  -- desired date range
                     , timestamp '2018-1-5'  -- preferably in ISO foramt
                     , interval '1 day') day
     , generate_series(1,5) repeat;          -- number of repetitions

这是一个 CROSS JOIN，生成一个 Carthesian 乘积，因此在示例中每个日期重复 5 次.输出类型是转换后的 date.为什么 timestamp 参数?见:

That's a CROSS JOIN, generating a Carthesian product, so each date is repeated 5 times in the example. Output is type is date after the cast. Why timestamp arguments? See:

• 在 PostgreSQL 中生成两个日期之间的时间序列

要获取您的特定日期格式，请使用 to_char():

To get your particular date format use to_char():

SELECT to_char(day, 'FMMM/FMDD/YYYY')
FROM   ...

默认情况下，这与您的示例一样排序，但这是一个实现细节.如果您需要保证特定订单，请添加ORDER BY.

This is ordered like your example by default, but that's an implementation detail. If you need to guarantee a particular order, add ORDER BY.

这篇关于生成具有重复日期的日期系列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！