I have a table that looks like below:
CREATE TABLE "time_records" ( startdate timestamp, starttime timestamp, endtime timestamp, timesheetid int, servicestate int, recordcount int ); There will be some overlaps within a day for the same timesheetid and servicestate. I have a count (recordcount) of the underlying records for each time range.
| startdate | starttime | endtime | timesheetid | servicestate | recordcount |
|---|---|---|---|---|---|
| 2024-01-30 00:00:00.000000 | 2024-01-30 09:00:00.000000 | 2024-01-30 13:00:00.000000 | 527915 | 2 | 1 |
| 2024-01-30 00:00:00.000000 | 2024-01-30 13:00:00.000000 | 2024-01-30 15:00:00.000000 | 527915 | 2 | 2 |
| 2024-01-30 00:00:00.000000 | 2024-01-30 14:00:00.000000 | 2024-01-30 15:00:00.000000 | 527915 | 2 | 1 |
What I'm trying to do is get distinct time ranges and the number of records for each range:
| startdate | starttime | endtime | timesheetid | servicestate | recordcount |
|---|---|---|---|---|---|
| 2024-01-30 00:00:00.000000 | 2024-01-30 09:00:00.000000 | 2024-01-30 13:00:00.000000 | 527915 | 2 | 1 |
| 2024-01-30 00:00:00.000000 | 2024-01-30 13:00:00.000000 | 2024-01-30 14:00:00.000000 | 527915 | 2 | 1 |
| 2024-01-30 00:00:00.000000 | 2024-01-30 14:00:00.000000 | 2024-01-30 15:00:00.000000 | 527915 | 2 | 2 |
I've gone done lot's of rabbit trails, but I haven't made any real progress to get there.
I'm stuck on postgresql 9.6 unfortunately.
