By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH test_table AS (
SELECT 1 AS id, 'ord1' AS name, TIMESTAMP WITH TIME ZONE '2019-02-01 07:00:00+00' AS occured_at UNION ALL
SELECT 2, 'ord1', TIMESTAMP WITH TIME ZONE '2019-02-01 12:30:00+00' UNION ALL
SELECT 3, 'ord1', TIMESTAMP WITH TIME ZONE '2019-02-02 06:58:00+00' UNION ALL
SELECT 4, 'ord1', TIMESTAMP WITH TIME ZONE '2019-02-02 07:01:00+00' UNION ALL
SELECT 5, 'ord1', TIMESTAMP WITH TIME ZONE '2019-02-03 07:00:00+00' UNION ALL
SELECT 6, 'ord1', TIMESTAMP WITH TIME ZONE '2019-02-04 06:59:00+00'
)
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;
| count | ord_name | ord_date |
|---|---|---|
| 3 | ord1 | 2019-02-01 00:00:00+00 |
| 1 | ord1 | 2019-02-02 00:00:00+00 |
| 2 | ord1 | 2019-02-03 00:00:00+00 |
SELECT 3