CODEWITHSUNDEEP
sqlpostgresql
Andrii Plotnikov
Andrii Plotnikov

Reputation: 3372

SQL difference between two days in hours per day

I have input: '2017-02-02 11:00:00' and '2017-02-13 15:00:00'

I want to know the difference in hours between these days, but there is a twist: I need to know the hours for each day.

the result would look something like

'2017-02-02', '13'
'2017-02-03', '24'
...
'2017-02-13', '15'

If it was only hours then I would use DATEDIFF, but assuming the twist I have no idea how to start. I would appreciate any idea.

ps: there can be different inputs, for example '2017-02-02 11:00:00' and '2017-02-02 15:00:00'

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One way uses generate_series() and some arithmetic:

with dates as (
      select gs.dte, $date1 as dte1, $date2 as dte2
      from generate_series(date_trunc('day', $date1), date_trunc('day', $date2), interval '1 day') gs(dte)
     )
select gs.dte,
       (case when dte1 > gs.dte and dte2 < gs.dte then 24
             when date_trunc('day', dte2) = gs.dte then 24 - extract(hour from dte1)
             else extract(hour from dte2)
        end) as hours
from dates;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use generate_series to generate all the datetimes with an interval of 1 hour between the start and end dates. Then you can group by the date part to get the number of hours worked each day.

select dt_time::date,count(*) as hours_by_day
from (select generate_series('2017-02-02 11:00:00','2017-02-13 15:00:00',interval '1 hour') as dt_time
     ) x
group by dt_time::date
order by 1

Upvotes: 1

Related Questions