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?

Erwin Brandstetter · Accepted Answer · 2020-06-19T22:23:53.873

3

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

edited Jun 19 '20 at 22:23

answered Nov 07 '11 at 21:49

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks a lot! works perfect! You were both right about the extract(). I ignored the Group, Limit and Order to simplify the question :) – Víctor Nov 07 '11 at 22:26
For those interested, removing that `LIMIT 1` let's you get a nice breakdown of all of the hours! This is great, thank you Erwin :) – Mark Pieszak - Trilon.io Jun 19 '20 at 21:10
1

@Mark: Notably, "all of the hours" can be simpler, yet: `SELECT extract(hour FROM done_at) AS h, count(*) AS ct FROM tbl GROUP BY 1 ORDER BY 2 DESC;`. – Erwin Brandstetter Jun 19 '20 at 22:06

score 1 · Answer 2 · answered Nov 07 '11 at 21:24

1

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

answered Nov 07 '11 at 21:24

lanzz

42,060
10
89
98

date_trunc() only zeroes all fields more-specific than the one you requested, but still returns a timestamp which is not comparable to an integer. extract() on the other hand returns the actual numeric value of the timestamp field you ask for, which is comparable to an integer. – lanzz Nov 07 '11 at 21:26

Grouping hours by moment of the day

2 Answers2

Major points: