CODEWITHSUNDEEP
postgresql
tester test
tester test

Reputation: 53

I want count of records group by time slot postgres

Suppose I have records in the table with created at DateTime column. then I want result look like

time window |  count
9am-3pm     |  9
3pm-6pm     |  12

where time window is time and count is a number if records created between this time.

Upvotes: 1

Views: 239

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use CASE WHEN with date_part function GROUP BY

Query 1:

SELECT CASE WHEN date_part('hour',dt) BETWEEN 3 AND 15 THEN '9am-3pm'
            WHEN date_part('hour',dt) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",
      COUNT(*)
FROM T
GROUP BY  CASE WHEN date_part('hour',dt) BETWEEN 3 AND 15 THEN '9am-3pm'
            WHEN date_part('hour',dt) BETWEEN 15 AND 18 THEN '3pm-6pm' END

Results:

| time window | count |
|-------------|-------|
|     3pm-6pm |     1 |
|     9am-3pm |     1 |

Upvotes: 2

Related Questions