CODEWITHSUNDEEP
sqlpostgresqlaggregationdate-math
Víctor
Víctor

Reputation: 666

Grouping hours by moment of the day

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?

Upvotes: 4

Views: 2373

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

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:

Upvotes: 3

lanzz
lanzz

Reputation: 43158

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

Upvotes: 1

Related Questions