Split time between two time stamps into hours

Database
Highlighted

Split time between two time stamps into hours

hello ,

 

I need help to split the time stamp into differnet hours like below. we want to calulate how many hours the employee has spent in a day in each hour.

 

Table structure

Name

STRT_TS

END_TS

Irfan

01MAY2017:05:56:00

01MAY2017:09:50:00

 

 

Expected result

NameSALES_HOURHours
irfan10 hours
irfan20 hours
irfan30 hours
irfan40 hours
irfan54 minutes or 0.07 hours 
irfan660 minutes or 1 hour
irfan760 minutes or 1 hour
irfan860 minutes or 1 hour
irfan950 minutes or 0.83 hour
irfan100 hours
irfan110 hours
irfan120 hours
irfan130 hours
irfan140 hours
irfan150 hours
irfan160 hours
irfan170 hours
irfan180 hours
irfan190 hours
irfan200 hours
irfan210 hours
irfan220 hours
irfan230 hours
irfan240 hours

Accepted Solutions
Junior Contributor

Re: Split time between two time stamps into hours

SELECT 
  NAME, Extract(HOUR From Begin(pd)), 
-- calculate the number of overlapping minutes within each hour Coalesce((INTERVAL(pd P_INTERSECT PERIOD(strt_ts,end_ts)) MINUTE) ,INTERVAL '0' MINUTE) FROM ( SELECT NAME, strt_ts, end_ts, pd FROM vt
-- create on row per hour for this date EXPAND ON PERIOD(TIMESTAMP '2017-05-01 00:00:00', TIMESTAMP '2017-05-02 00:00:00') AS pd BY ANCHOR PERIOD Anchor_Hour ) AS dt
1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: Split time between two time stamps into hours

SELECT 
  NAME, Extract(HOUR From Begin(pd)), 
-- calculate the number of overlapping minutes within each hour Coalesce((INTERVAL(pd P_INTERSECT PERIOD(strt_ts,end_ts)) MINUTE) ,INTERVAL '0' MINUTE) FROM ( SELECT NAME, strt_ts, end_ts, pd FROM vt
-- create on row per hour for this date EXPAND ON PERIOD(TIMESTAMP '2017-05-01 00:00:00', TIMESTAMP '2017-05-02 00:00:00') AS pd BY ANCHOR PERIOD Anchor_Hour ) AS dt

Re: Split time between two time stamps into hours

@dnoeth : Thanks for the quick response , the soution worked like a charm . i just had to replace the hard coded period variables to my start and end time .

you made my day .

Thanks again.

Irfan


dnoeth wrote:
SELECT 
  NAME, Extract(HOUR From Begin(pd)), 
-- calculate the number of overlapping minutes within each hour Coalesce((INTERVAL(pd P_INTERSECT PERIOD(strt_ts,end_ts)) MINUTE) ,INTERVAL '0' MINUTE) FROM ( SELECT NAME, strt_ts, end_ts, pd FROM vt
-- create on row per hour for this date EXPAND ON PERIOD(TIMESTAMP '2017-05-01 00:00:00', TIMESTAMP '2017-05-02 00:00:00') AS pd BY ANCHOR PERIOD Anchor_Hour ) AS dt

 

Re: Split time between two time stamps into hours

@dnoeth

I am getting begining bound must be greater than ending bound error if i have multiple rows of data. how do i expand it for multiple values

 

from above example

 

NameSTRT_TSEND_TS
Irfan01MAY2017:05:56:0001MAY2017:09:50:00
Irfan02MAY2017:05:56:0102MAY2017:09:50:01
Dave02MAY2017:05:56:0202MAY2017:09:50:02
Dave01MAY2017:05:56:0001MAY2017:09:50:00
Junior Contributor

Re: Split time between two time stamps into hours

This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:

select *
from tab
where STRT_TS >= END_TS

If this returns row you must decide how to eal with that.

 

Re: Split time between two time stamps into hours

@dnoeth Thanks , yes it was the data issue as you mentioned as start and end time was same


dnoeth wrote:

This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:

select *
from tab
where STRT_TS >= END_TS

If this returns row you must decide how to eal with that.

 



dnoeth wrote:

This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:

select *
from tab
where STRT_TS >= END_TS

If this returns row you must decide how to eal with that.

 




and i was able to fix it.