sql - Count minutes between first and last tuple and group by username -


i have table stores data regarding users visit system. user browsing system, storing location, username, date , time in oracle 11g database. table looks this:

    username   date           time          location      user1      yyyy-mm-dd    hh24:mi:ss     kitchen     user2      yyyy-mm-dd    hh24:mi:ss     bathroom 

i need query calculate minutes between first , last log of each day, each particular username. how 1 go doing that?

assuming date , time varchar2,
try:

with t (    select username, "date", to_date("date"||time, 'yyyy-mm-ddhh24:mi:ss') dt    your_table  ) select username, "date",  (max(dt)- min(dt)) * 1440 t group username, "date" 

here sqlfiddle demo


Comments