CODEWITHSUNDEEP
postgresqldate-arithmeticseconds
Victor Castillo Torres
Victor Castillo Torres

Reputation: 10811

How many seconds passed by grouped by hour between two dates

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)

Upvotes: 0

Views: 45

Answers (1)

Rémy Baron
Rémy Baron

Reputation: 1399

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

Upvotes: 1

Related Questions