How many seconds passed by grouped by hour between two dates

Question

Let's suppose I have a start date 2016-06-19 09:30:00 and an end date 2016-06-19 10:20:00

I would like to get the time that elapsed every hour before starting the next hour or before getting to the final time in seconds grouped by hour and date, the result I'm trying to achieve (without having any success) would be something like this:

hour |    date    | time_elapsed_in_seconds
 9   | 2016-06-19 |  1800 (there are 1800 seconds between 09:30:00 and 10:00:00)
 10  | 2016-06-19 |  1200 (there are 1200 seconds between 10:00:00 and 10:20:00)

Answer 1

Try this :

        with table1 as (
      select '2016-06-19 09:30:00'::timestamp without time zone start_date,'2016-06-19 10:20:00'::timestamp without time zone end_date
      )


    select extract(hour from the_hour) "hour",the_hour::date "date",extract (epoch from (new_end-new_start)) "time_elapsed" from (
        select the_hour,CASE WHEN date_trunc('hour',start_date)=the_hour then start_date else the_hour end new_start,
                    CASE WHEN date_trunc('hour',end_date)=the_hour then end_date else the_hour+'1 hour'::interval end new_end 
           from (
              select generate_series(date_trunc('hour',start_date),end_date,'1 hour'::interval) the_hour,start_date,end_date from table1 
            ) a 
         ) b