postgresql - 在 postgres 中为 time 生成系列结果的正确方法

Question

我想在 postgres 中使用 generate_series 函数，这样我就可以得到这样的数据，这个想法是用 1 秒的减法得到范围间隔

[
        {
            "generate_series": "2021-09-24T19:00:00.000Z"
        },
        {
            "generate_series": "2021-09-24T19:14:59.000Z"
        },
        {
            "generate_series": "2021-09-24T19:15:00.000Z"
        },
        {
            "generate_series": "2021-09-24T19:29:59.000Z"
        },
        {
            "generate_series": "2021-09-24T20:30:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:44:59.000Z"
        },
        {
            "generate_series": "2021-09-24T20:45:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:59:59.000Z"
        }
    ]

我用这个公式，SELECT * from generate_series(('02:00:00'::time + '2021-09-25'::date)::timestamp,('04:00:00'::time + '2021-09-25'::date)::timestamp, interval '15m');

但是，这是我得到的结果

[
        {
            "generate_series": "2021-09-24T19:00:00.000Z"
        },
        {
            "generate_series": "2021-09-24T19:15:00.000Z"
        },
        {
            "generate_series": "2021-09-24T19:30:00.000Z"
        },
        {
            "generate_series": "2021-09-24T19:45:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:00:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:15:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:30:00.000Z"
        },
        {
            "generate_series": "2021-09-24T20:45:00.000Z"
        },
        {
            "generate_series": "2021-09-24T21:00:00.000Z"
        }
    ]

如何在 postgres 中使用 generate_series 获得第一个结果？

标签： postgresqldategenerate-series

Answer 1

select 
  g - (CASE WHEN r>1 THEN 1 ELSE 0 END  * INTERVAL '1 SECOND') 
from (
   select 
      row_number() over (order by generate_series) r, 
      generate_series g 
   from generate_series('2021-09-24T19:00:00.000Z'::timestamp, '2021-09-24T20:59:59.000Z'::timestamp,INTERVAL '15 minutes')
) as x;

输出：

      ?column?
---------------------
 2021-09-24 19:00:00
 2021-09-24 19:14:59
 2021-09-24 19:29:59
 2021-09-24 19:44:59
 2021-09-24 19:59:59
 2021-09-24 20:14:59
 2021-09-24 20:29:59
 2021-09-24 20:44:59
(8 rows)

如果您想要 JSON 格式的输出：

select json_agg(x) from (
    select 
  g - (CASE WHEN r>1 THEN 1 ELSE 0 END  * INTERVAL '1 SECOND') as "generate_series"
from (
   select 
      row_number() over (order by generate_series) r, 
      generate_series g 
   from generate_series('2021-09-24T19:00:00.000Z'::timestamp, '2021-09-24T20:59:59.000Z'::timestamp,INTERVAL '15 minutes')
) x ) x;

输出：

[{"generate_series":"2021-09-24T19:00:00"},
 {"generate_series":"2021-09-24T19:14:59"},
 {"generate_series":"2021-09-24T19:29:59"},
 {"generate_series":"2021-09-24T19:44:59"},
 {"generate_series":"2021-09-24T19:59:59"},
 {"generate_series":"2021-09-24T20:14:59"},
 {"generate_series":"2021-09-24T20:29:59"},
 {"generate_series":"2021-09-24T20:44:59"}]