Grouping hours by moment of the day

Question

I would need to fetch the most common moment of the day (dawn, morning, evening, night) in a group of records. I'm trying to group the results in the moments using CASE-WHEN, but I'm unable to compare the date and an integer. I try this:

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;

But get this error:

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

I also tried to cast the type to an integer, but I get this:

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

The thing is that done_at does have a time zone. From the Rails console:

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

And now I'm clueless. How to get the moment of the day?

Answer 1

Query could look like this:

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;

Major points:

• @lanzz is right about extract().
• Use a subquery to calculate the hour of the day to greatly simplify the code.
• GROUP BY, count(), ORDER BY and LIMIT can happen at one query level.
• Result is the the most common moment of the day as requested.

done_at is obviously type timestamptz. So the extracted hour depends on the time zone setting of the current session. To remove this dependency, you may want to define the time zone to work with explicitly:

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

See:

• Ignoring time zones altogether in Rails and PostgreSQL

Answer 2

You want extract(hour from done_at) instead of date_trunc('hour', done_at)