On Mar 16, 2015, at 2:13 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > > On 03/16/2015 02:57 PM, Israel Brewster wrote: >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by "hour of day", that is the >> record should be included in the group if the hour of the day for the >> group falls anywhere in the range [start,end]. Obviously each record may >> well fall into multiple groups under this scenario. >> >> The goal here is to figure out, for each hour of the day, a) what is the >> total number of "active" records for that hour, and b) what is the total >> "active" time for those records during the hour, with an ultimate goal >> of figuring out the average active time per record per hour. >> >> So, for simplified example, if the table contained three records: >> >> start | end >> ----------------------------------------------------- >> 2015-03-15 08:15 | 2015-03-15 10:45 >> 2015-03-15 09:30 | 2015-03-15 10:15 >> 2015-03-15 10:30 | 2015-03-15 11:30 >> >> >> Then the results should break out something like this: >> >> hour | count | sum >> ----------------------------- >> 8 | 1 | 0.75 >> 9 | 2 | 1.5 >> 10 | 3 | 1.5 >> 11 | 1 | 0.5 >> >> I can then easily manipulate these values to get my ultimate goal of the >> average, which would of course always be less than or equal to 1. Is >> this doable in postgress? Or would it be a better idea to simply pull >> the raw data and post-process in code? Thanks. > > Do not have an answer for you, but a question: > > What version of Postgres are you on? > > This will help determine what tools are available to work with. Oh, right. Of course. I'm on 9.4.0 ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ----------------------------------------------- > >> >> ----------------------------------------------- >> Israel Brewster >> Systems Analyst II >> Ravn Alaska >> 5245 Airport Industrial Rd >> Fairbanks, AK 99709 >> (907) 450-7293 >> ----------------------------------------------- >> >> >> >> >> > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
