Given Start Time , End Time & Secs. Obtain secs in a specific Hour

Question

I'm using Vertica Database. I am trying to get the total secs in a particular hour from the following example session data. Any sample SQL code would be very helpful - Thanks

         start time        end time           session length(secs) 
 2010-02-21 20:30:00      2010-02-21 23:30:00    10800
 2010-02-21 21:30:00     2010-02-21 22:30:00     3600
 2010-02-21 21:45:00      2010-02-21 21:59:00      840
 2010-02-21 22:00:00     2010-02-21 22:20:00     1200
 2010-02-21 22:30:00      2010-02-21 23:30:00     3600

Desired Output

hour   secs_in_that_hour
20         1800
21         6240
22         8400
23         3600

Answer 1

You would need a table containing every hour, so that you could join it in. That join would be based on the hour being within start and end time and then you can extract the time using (min(hour end,end time) - max(hour start,start time)). Then group on the hour and sum.

Since I don't know vertica, I have no complete answer to this.

Answer 2

Vertica is based on PostgresSQL, especially language-wise. The best thing you could do is look up Postgres's Date Time functions and related tutorials. I haven't found an instance where a Postgres time function does not work in Vertica.

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

There is probably a datediff type function you can use. (Sorry, I don't have to time to look it up.)