Skip to Main Content

Query to split time (start_time+duration) into hourly intervals (how many minutes for each hour)

user2013716Mar 31 2016 — edited Apr 20 2016

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_IDSTART_TIMEDURATION
106/01/2016 12:00:0030
106/01/2016 12:40:00 70
107/01/2016 16:05:00 150
206/01/2016 10:30:00 240
206/01/2016 12:50:00 5
206/01/2016 14:10:55 60

EXPECTED OUTPUT SAMPLE:

USER_IDSTART_TIMEHOUR_INTERVALMINUTES
106/01/2016 12:00:001230
106/01/2016 12:40:001220
106/01/2016 12:40:001350
107/01/2016 16:05:001655
107/01/2016 16:05:001760
107/01/2016 16:05:001835
206/01/2016 10:30:001030
206/01/2016 10:30:001160
206/01/2016 10:30:001260
206/01/2016 10:30:001360
206/01/2016 10:30:001430
206/01/2016 12:50:00125
206/01/2016 14:10:551451
206/01/2016 14:10:55159

Kind Regards,

This post has been answered by Frank Kulash on Mar 31 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on May 18 2016
Added on Mar 31 2016
8 comments
1,006 views