PostgreSQL如何将SELECT查询按12小时时段拆分获取分时段数据

你可以根据需求选择分开查询或者合并查询两种方案：

方案1：分开两次查询分别获取两个时段数据 #

时段1：2021-10-24 00:00 ~ 12:00 #

SELECT 
  py.id, 
  py.external_id, 
  py.created_dt
FROM payment AS py
INNER JOIN services AS sv ON sv.id = py.service_id
INNER JOIN source_types AS st ON st.id = py.source_type_id
INNER JOIN agents AS ag ON ag.id = py.agent_id
INNER JOIN groups AS gr ON gr.id = ag.group_id
WHERE py.created_dt >= '2021-10-24 00:00:00' 
  AND py.created_dt < '2021-10-24 12:00:00'

时段2：2021-10-24 12:00 ~ 次日00:00 #

SELECT 
  py.id, 
  py.external_id, 
  py.created_dt
FROM payment AS py
INNER JOIN services AS sv ON sv.id = py.service_id
INNER JOIN source_types AS st ON st.id = py.source_type_id
INNER JOIN agents AS ag ON ag.id = py.agent_id
INNER JOIN groups AS gr ON gr.id = ag.group_id
WHERE py.created_dt >= '2021-10-24 12:00:00' 
  AND py.created_dt < '2021-10-25 00:00:00'

方案2：一次查询返回两个时段所有数据（可选附带时段标识） #

如果需要同时拿到两个时段的数据，并且标注所属时段，可以用以下语句：

SELECT 
  py.id, 
  py.external_id, 
  py.created_dt,
  CASE 
    WHEN py.created_dt < '2021-10-24 12:00:00' THEN '00:00-12:00'
    ELSE '12:00-24:00'
  END AS time_period
FROM payment AS py
INNER JOIN services AS sv ON sv.id = py.service_id
INNER JOIN source_types AS st ON st.id = py.source_type_id
INNER JOIN agents AS ag ON ag.id = py.agent_id
INNER JOIN groups AS gr ON gr.id = ag.group_id
WHERE py.created_dt >= '2021-10-24' 
  AND py.created_dt < '2021-10-25'

如果不需要时段标识，直接去掉CASE对应的time_period字段即可。

内容的提问来源于stack exchange，提问作者Alexander