Hi,
From a start_time and duration (in minutes), I've tried to split the total time into hourly intervals but don't really know how to do it.
Here's a sample table script and a to-do-query output:
CREATE TABLE timetest (id number, start_time date, duration number);
INSERT INTO timetest (id,start_time,duration) VALUES (1,to_date('06/01/2016 12:00:00','dd/mm/yyyy hh24:mi:ss'),30);
INSERT INTO timetest (id,start_time,duration) VALUES (1,to_date('06/01/2016 12:40:00','dd/mm/yyyy hh24:mi:ss'),70);
INSERT INTO timetest (id,start_time,duration) VALUES (1,to_date('07/01/2016 16:00:00','dd/mm/yyyy hh24:mi:ss'),150);
INSERT INTO timetest (id,start_time,duration) VALUES (2,to_date('06/01/2016 10:30:00','dd/mm/yyyy hh24:mi:ss'),240);
INSERT INTO timetest (id,start_time,duration) VALUES (2,to_date('06/01/2016 12:50:00','dd/mm/yyyy hh24:mi:ss'),5);
INSERT INTO timetest (id,start_time,duration) VALUES (2,to_date('06/01/2016 14:10:55','dd/mm/yyyy hh24:mi:ss'),60);
TABLE:
USER_ID | START_TIME | DURATION |
---|
1 | 06/01/2016 12:00:00 | 30 | 1 | 06/01/2016 12:40:00 | 70 | 1 | 07/01/2016 16:05:00 | 150 | 2 | 06/01/2016 10:30:00 | 240 | 2 | 06/01/2016 12:50:00 | 5 | 2 | 06/01/2016 14:10:55 | 60 |
| |
EXPECTED OUTPUT SAMPLE:
USER_ID | START_TIME | HOUR_INTERVAL | MINUTES |
---|
1 | 06/01/2016 12:00:00 | 12 | 30 |
1 | 06/01/2016 12:40:00 | 12 | 20 |
1 | 06/01/2016 12:40:00 | 13 | 50 |
1 | 07/01/2016 16:05:00 | 16 | 55 |
1 | 07/01/2016 16:05:00 | 17 | 60 |
1 | 07/01/2016 16:05:00 | 18 | 35 |
2 | 06/01/2016 10:30:00 | 10 | 30 |
2 | 06/01/2016 10:30:00 | 11 | 60 |
2 | 06/01/2016 10:30:00 | 12 | 60 |
2 | 06/01/2016 10:30:00 | 13 | 60 |
2 | 06/01/2016 10:30:00 | 14 | 30 |
2 | 06/01/2016 12:50:00 | 12 | 5 |
2 | 06/01/2016 14:10:55 | 14 | 51 |
2 | 06/01/2016 14:10:55 | 15 | 9 |