我需要获取一组记录中最常见的时刻(黎明、上午、傍晚、夜间)。我试图使用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
现在我很困惑。如何获取一天中的时刻?
LIMIT 1
可以让您获得所有小时的很好的细分!这太棒了,谢谢 Erwin :) - Mark Pieszak - Trilon.ioSELECT extract(hour FROM done_at) AS h, count(*) AS ct FROM tbl GROUP BY 1 ORDER BY 2 DESC;
。 - Erwin Brandstetter