getting first column blank postgres

Question

SELECT CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' 
            WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",COUNT(*) FROM tickets where created_at < now()
GROUP BY  CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END;

 time window | count 
-------------+-------
             |     6
 9am-3pm     |    69

is it possible to filter it by date along with time so that my result set will looks like

Date        | time window | count 
------------+-------------+-------
12-01-2020  |      9am-3pm|     6
12-01-2020  |      3pm-6pm|    69
13-01-2020  |      9am-3pm|    12
13-01-2020  |      3pm-6pm|    14

Tim Biegeleisen · Accepted Answer · 2020-01-13 06:05:16Z

1

We can handle this requirement using a calendar table approach:

WITH dates AS (
    SELECT '12-01-2020' AS created_at UNION ALL
    SELECT '13-01-2020'
),
tw AS (
    SELECT '9am-3pm' AS "time window" UNION ALL
    SELECT '3pm-6pm'
),
cte AS (
    SELECT
        created_at::date AS created_at,
        CASE WHEN DATE_PART('hour', created_at) BETWEEN 3 AND 15 THEN '9am-3pm'
             WHEN DATE_PART('hour', created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",
        COUNT(*) AS cnt
    FROM tickets
    WHERE created_at < NOW()
    GROUP BY 1, 2
)

SELECT
    d.created_at,
    tw."time window",
    COALESCE(t.cnt, 0) AS count
FROM dates d
CROSS JOIN tw
LEFT JOIN cte t
    ON d.created_at = t.created_at AND tw."time window" = t."time window"
ORDER BY
    d.dt,
    tw."time window";

edited Jan 13, 2020 at 6:05

answered Jan 13, 2020 at 5:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danny Over a year ago

looking for same query as return by me : ``` SELECT CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END "time window",COUNT(*) FROM tickets where created_at < now() GROUP BY CASE WHEN date_part('hour',created_at) BETWEEN 3 AND 15 THEN '9am-3pm' WHEN date_part('hour',created_at) BETWEEN 15 AND 18 THEN '3pm-6pm' END; ``` it is fine if there is no date column main thing is time window and count

Laurenz Albe · Accepted Answer · 2020-01-13 06:21:52Z

0

You are actually asking two questions:

The "empty space" (really an SQL NULL) is there because there are dates that do not fall within any of the time ranges. You can exclude them with an additional WHERE condition.
To get the date part as well, add
```
CAST (created_at AS date)
```
to the SELECT list and the GROUP BY ckause.

answered Jan 13, 2020 at 6:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

Note that this approach might result in missing dates/slots should the OP's table have no data there.

Collectives™ on Stack Overflow

getting first column blank postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related