CODEWITHSUNDEEP
sqlpostgresqlpsql
Kishore Mohan
Kishore Mohan

Reputation: 1060

Get total utilised time on every hour using Postgres

Input:

-------------------------------------------
start_time           |  end_time           
-------------------------------------------
2019-03-28 04:00:00  |  2019-03-28 04:15:00
2019-03-28 04:00:00  |  2019-03-28 04:30:00
2019-03-28 04:40:00  |  2019-03-28 04:50:00
2019-03-28 04:50:00  |  2019-03-28 05:15:00
2019-03-28 05:15:00  |  2019-03-28 05:45:00
2019-03-28 06:15:00  |  2019-03-28 06:45:00
2019-03-28 07:00:00  |  2019-03-28 08:00:00
2019-03-29 04:00:00  |  2019-03-29 04:15:00
2019-03-29 04:00:00  |  2019-03-29 04:30:00
2019-03-29 04:40:00  |  2019-03-29 04:50:00
2019-03-29 04:50:00  |  2019-03-29 05:15:00
2019-03-29 05:15:00  |  2019-03-29 05:45:00
2019-03-29 06:15:00  |  2019-03-29 06:45:00
2019-03-29 07:00:00  |  2019-03-29 08:00:00

I want to get total utilised time for an every hour on across dates. Duration can overlap between on same duration as well.

expecting output:

------------------------------------------------------------
duration                                 |  utilised_time
------------------------------------------------------------
2019-03-28 12:00 - 2019-03-28 01:00      |    0
2019-03-28 01:00 - 2019-03-28 02:00      |    0
2019-03-28 02:00 - 2019-03-28 03:00      |    0
2019-03-28 03:00 - 2019-03-28 04:00      |    0
2019-03-28 04:00 - 2019-03-28 05:00      |    50
2019-03-28 05:00 - 2019-03-28 06:00      |    45
2019-03-28 06:00 - 2019-03-28 07:00      |    45
2019-03-28 07:00 - 2019-03-28 08:00      |    60
2019-03-28 08:00 - 2019-03-28 09:00      |    0
2019-03-29 12:00 - 2019-03-29 01:00      |    0
2019-03-29 01:00 - 2019-03-29 02:00      |    0
2019-03-29 02:00 - 2019-03-29 03:00      |    0
2019-03-29 03:00 - 2019-03-29 04:00      |    0
2019-03-29 04:00 - 2019-03-29 05:00      |    50
2019-03-29 05:00 - 2019-03-29 06:00      |    45
2019-03-29 06:00 - 2019-03-29 07:00      |    45
2019-03-29 07:00 - 2019-03-29 08:00      |    60
2019-03-29 08:00 - 2019-03-29 09:00      |    0

How we can achieve this using PSQL?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can generate the hours using generate_series(). Then use a left join and aggregation:

select g.ts, g.ts + interval '1 hour',
       sum(extract(epoch from (least(g.ts + interval '1 hour', i.end_time) -
                               greatest(g.ts, i.start_time)
                              )
                   )
          ) / 60 as duration_in_minutes
from generate_series('2019-03-28 12:00:00'::timestamp, '2019-03-29 08:00 - 2019-03-29 09:00:00'::timestamp, interval '1 hour'
                    ) g(ts) left join
       input i
       on i.start_time < g.ts + interval '1 hour' and
          i.end_time > g.ts
group by g.ts
order by g.ts;

Your results and your question are unclear about what hours you want. You explicitly state "I want to get total utilised time for an every hour on across dates." However, your sample results do not have every hour. You can filter the hours you want using a where clause.

Upvotes: 2

Related Questions