CODEWITHSUNDEEP
sqlpostgresqlgroup-bycount
Paolo
Paolo

Reputation: 853

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

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|

Upvotes: 1

Views: 1678

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522731

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;

enter image description here

Demo

Upvotes: 3

Related Questions