按照一天的时间段分组计算小时数

Question

我需要获取一组记录中最常见的时刻（黎明、上午、傍晚、夜间）。我试图使用CASE-WHEN将结果分组到不同时刻，但我无法比较日期和整数。我尝试了以下方法：

SELECT done_at,
  case done_at
  when date_trunc('hour', done_at) > 0 and date_trunc('hour', done_at) <= 7 then
    'dawn'
  when dayhour > 7 and dayhour <= 12 then
    'morning'
  when dayhour > 12 and dayhour <= 20 then
    'evening'
  when dayhour > 20 and dayhour <= 00 then
    'night'
  end
FROM iterations;

但是出现了以下错误：

ERROR:  operator does not exist: timestamp without time zone > integer
LINE 3:       when date_trunc('hour', done_at) > 0 and date_trunc('h...

我也尝试将类型转换为整数，但是出现了这个错误：

ERROR:  cannot cast type timestamp without time zone to integer
LINE 3:       when date_trunc('hour', done_at)::integer > 0 and date...

事实是 done_at 确实有一个时区。从Rails控制台：

?> Iteration.first.done_at
=> Fri, 23 Sep 2011 02:00:00 CEST +02:00

现在我很困惑。如何获取一天中的时刻？

- Víctor

Answer 1

查询可能会像这样：

SELECT CASE 
        WHEN h >= 0  AND h <= 7  THEN 'dawn'  -- including 0 !
        WHEN h >  7  AND h <= 12 THEN 'morning'
        WHEN h > 12  AND h <= 20 THEN 'evening'
        WHEN h > 20              THEN 'night'
       END AS moment
     , count(*) AS cnt
FROM  (
    SELECT extract(hour from done_at) AS h
    FROM   iterations
    ) x
GROUP  BY 1
ORDER  BY count(*) DESC
LIMIT  1;

主要要点：

• @lanzz在extract()方面是正确的。
• 使用子查询来计算一天中的小时数，可以大大简化代码。
• GROUP BY、count()、ORDER BY和LIMIT可以在一个查询级别上完成。
• 结果是所请求的一天中最常见的时间。

done_at显然是timestamptz类型。因此，提取的小时取决于当前会话的时区设置。为了消除这种依赖关系，您可能需要明确定义要使用的时区：

extract(hour FROM done_at AT TIME ZONE 'Europe/Vienna') AS h

请参考：

• 在Rails和PostgreSQL中完全忽略时区

- Erwin Brandstetter

---

非常感谢！完美运作！你们两个都对 extract() 有正确的理解。我忽略了 Group、Limit 和 Order 条件，以简化问题 :) - Víctor

对于那些感兴趣的人，删除 LIMIT 1 可以让您获得所有小时的很好的细分！这太棒了，谢谢 Erwin :) - Mark Pieszak - Trilon.io

@Mark：值得注意的是，“所有小时”可以更简单，如下所示：SELECT extract(hour FROM done_at) AS h, count(*) AS ct FROM tbl GROUP BY 1 ORDER BY 2 DESC;。 - Erwin Brandstetter

Answer 2

你需要使用extract(hour from done_at)而不是date_trunc('hour', done_at)

- lanzz

---

date_trunc() 只将比所要求的字段更具体的所有字段归零，但仍返回一个时间戳，无法与整数进行比较。另一方面，extract() 返回您所请求的时间戳字段的实际数字值，可与整数进行比较。 - lanzz