时间戳

首页 > 解决方案 > 如何在 Postgres 中指定的时间内查看每小时结果

sql - 如何在 Postgres 中指定的时间内查看每小时结果

问题描述

当指定以下条件时，我想创建一个返回时区列表的查询。

select day, hour
...
where
  target_datetime between '2021-08-01 00:00:00' and '2021-08-01 23:59:59'

[
  { 'day': '2021-08-01', 'hour': 0 },
  { 'day': '2021-08-01', 'hour': 1 },
  { 'day': '2021-08-01', 'hour': 2 },
...
  { 'day': '2021-08-01', 'hour': 23 }
]

我怎样才能得到这个？

标签： sqlpostgresql

解决方案

用于generate_series从时间间隔创建记录，并jsonb_build_objebt用于jsonb_agg创建您的 json 文档：

SELECT 
  jsonb_agg(
    jsonb_build_object(
      'day',tm::date,
      'hour',EXTRACT(HOUR FROM tm)))
FROM generate_series('2021-08-01 00:00:00'::timestamp, 
                     '2021-08-01 23:59:59'::timestamp,
                      interval '1 hour') j (tm);

演示：db<>fiddle

推荐阅读