PostgreSQL: Record count for each day in a week given a specific time interval

Question

I'm trying to get the number of records for each day given a certain time interval specifically (>= 7 AM Monday and < 7AM of Tuesday, >= 7 AM Tuesday and 7 < AM Wednesday) and the pattern goes on. This is my table structure

TABLE Name: test_table
|---------------------|------------------|-------------------------|
|          id         |        name      |        occured_at       |
|---------------------|------------------|-------------------------|

I can count how many records I have for each day using the query below but I'm pretty sure that it only counts entries between 12 AM and 11:59 PM of the same day.

SELECT COUNT(id),name AS ord_name, date_trunc('day', occured_at) AS ord_date
FROM test_table
GROUP BY ord_name, ord_date
ORDER BY ord_date

Result:

|count|ord_name|        ord_date      |
|-----|--------|----------------------|
| 20  |  ord1  |2019-02-01 00:00:00+00|
| 25  |  ord1  |2019-02-02 00:00:00+00|
| 30  |  ord1  |2019-02-03 00:00:00+00|
| 15  |  ord1  |2019-02-04 00:00:00+00|

I have also tried using INTERVAL by adding 24 hours into my occured_at COLUMN but I wasn't able to make it work.

SELECT COUNT(id),name AS ord_name, date_trunc('day', occured_at) AS ord_date
FROM test_table
WHERE ord_date::time >= '07:00:00' + INTERVAL '24 hour'
GROUP BY ord_name, ord_date
ORDER BY ord_date

Sample Data:

| id  |  name  |       occured_at     |
|-----|--------|----------------------|
| 1   |  ord1  |2019-02-01 07:00:00+00|
| 2   |  ord1  |2019-02-01 12:30:00+00|
| 3   |  ord1  |2019-02-02 06:58:00+00|
| 4   |  ord1  |2019-02-02 07:01:00+00|
| 5   |  ord1  |2019-02-03 07:00:00+00|
| 6   |  ord1  |2019-02-04 06:59:00+00|

Expected Result:

|count |ord_name |ord_date  |
|------|---------|----------|
| 3    |  ord1   |2019-02-01|
| 1    |  ord1   |2019-02-02|
| 2    |  ord1   |2019-02-03|

Can you add example input data and the expected output for that data? — Dimitar Spasovski
– Dimitar Spasovski, Commented Feb 12, 2019 at 14:49

Tim Biegeleisen · Accepted Answer · 2019-02-12 15:22:10Z

3

Since your day logically begins at 7am in this case, you may rephrase your dates by subtracting 7 hours from them. This way, a date at exactly 7am would appear to be the start of that actual day.

SELECT
    COUNT(id),
    name AS ord_name,
    date_trunc('day', occured_at - interval '7 hour') AS ord_date
FROM test_table
GROUP BY
    name,
    date_trunc('day', occured_at - interval '7 hour')
ORDER BY
    ord_date;

Demo

edited Feb 12, 2019 at 15:22

answered Feb 12, 2019 at 14:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

Paolo Over a year ago

Hello Tim, I've tried the query but it doesn't group the records by ord_date

Tim Biegeleisen Over a year ago

@Paolo Your initial question was lacking data, and I didn't bother to check my answer. I have edited my answer using your sample data and it seems to work now.

Paolo Over a year ago

I apologize for not adding the sample data and expected output initially, I've tested your query and it definitely works. Never thought about that approach of subtracting 7 hours.

Collectives™ on Stack Overflow

PostgreSQL: Record count for each day in a week given a specific time interval

1 Answer 1

Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related