topic Re: Split time between two time stamps into hours in Database
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74884#M31136
<P><LI-USER uid="223" login="dnoeth"></LI-USER> : 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 .</P><P>you made my day .</P><P>Thanks again.</P><P>Irfan</P><BLOCKQUOTE><HR />dnoeth wrote:<BR /><PRE>SELECT
NAME, Extract(HOUR From Begin(pd)), <BR /> -- 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<BR /> -- 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</PRE><HR /></BLOCKQUOTE><P> </P>Wed, 02 Aug 2017 09:37:27 GMTirfuhoney2017-08-02T09:37:27ZSplit time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74860#M31132
<P>hello ,</P><P> </P><P>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.</P><P> </P><P>Table structure</P><TABLE><TBODY><TR><TD>Name</TD><TD><P>STRT_TS</P></TD><TD><P>END_TS</P></TD></TR><TR><TD><P>Irfan</P></TD><TD><P>01MAY2017:05:56:00</P></TD><TD><P>01MAY2017:09:50:00</P></TD></TR></TBODY></TABLE><P> </P><P> </P><P>Expected result</P><TABLE><TBODY><TR><TD>Name</TD><TD>SALES_HOUR</TD><TD>Hours</TD></TR><TR><TD>irfan</TD><TD>1</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>2</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>3</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>4</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>5</TD><TD>4 minutes or 0.07 hours </TD></TR><TR><TD>irfan</TD><TD>6</TD><TD>60 minutes or 1 hour</TD></TR><TR><TD>irfan</TD><TD>7</TD><TD>60 minutes or 1 hour</TD></TR><TR><TD>irfan</TD><TD>8</TD><TD>60 minutes or 1 hour</TD></TR><TR><TD>irfan</TD><TD>9</TD><TD>50 minutes or 0.83 hour</TD></TR><TR><TD>irfan</TD><TD>10</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>11</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>12</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>13</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>14</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>15</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>16</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>17</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>18</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>19</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>20</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>21</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>22</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>23</TD><TD>0 hours</TD></TR><TR><TD>irfan</TD><TD>24</TD><TD>0 hours</TD></TR></TBODY></TABLE>Tue, 01 Aug 2017 12:02:59 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74860#M31132irfuhoney2017-08-01T12:02:59ZRe: Split time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74867#M31133
<PRE>SELECT
NAME, Extract(HOUR From Begin(pd)), <BR /> -- 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<BR /> -- 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</PRE>Tue, 01 Aug 2017 14:10:09 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74867#M31133dnoeth2017-08-01T14:10:09ZRe: Split time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74884#M31136
<P><LI-USER uid="223" login="dnoeth"></LI-USER> : 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 .</P><P>you made my day .</P><P>Thanks again.</P><P>Irfan</P><BLOCKQUOTE><HR />dnoeth wrote:<BR /><PRE>SELECT
NAME, Extract(HOUR From Begin(pd)), <BR /> -- 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<BR /> -- 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</PRE><HR /></BLOCKQUOTE><P> </P>Wed, 02 Aug 2017 09:37:27 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/74884#M31136irfuhoney2017-08-02T09:37:27ZRe: Split time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75051#M31218
<P><LI-USER uid="223" login="dnoeth"></LI-USER></P><P>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</P><P> </P><P>from above example</P><P> </P><TABLE><TBODY><TR><TD>Name</TD><TD>STRT_TS</TD><TD>END_TS</TD></TR><TR><TD>Irfan</TD><TD>01MAY2017:05:56:00</TD><TD>01MAY2017:09:50:00</TD></TR><TR><TD>Irfan</TD><TD>02MAY2017:05:56:01</TD><TD>02MAY2017:09:50:01</TD></TR><TR><TD>Dave</TD><TD>02MAY2017:05:56:02</TD><TD>02MAY2017:09:50:02</TD></TR><TR><TD>Dave</TD><TD>01MAY2017:05:56:00</TD><TD>01MAY2017:09:50:00</TD></TR></TBODY></TABLE>Wed, 09 Aug 2017 14:03:45 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75051#M31218irfuhoney2017-08-09T14:03:45ZRe: Split time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75066#M31228
<P>This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:</P><PRE>select *
from tab
where STRT_TS >= END_TS</PRE><P>If this returns row you must decide how to eal with that.</P><P> </P>Wed, 09 Aug 2017 22:10:32 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75066#M31228dnoeth2017-08-09T22:10:32ZRe: Split time between two time stamps into hours
http://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75094#M31247
<P><LI-USER uid="223" login="dnoeth"></LI-USER> Thanks , yes it was the data issue as you mentioned as start and end time was same</P><BLOCKQUOTE><HR />dnoeth wrote:<BR /><P>This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:</P><PRE>select *
from tab
where STRT_TS >= END_TS</PRE><P>If this returns row you must decide how to eal with that.</P><P> </P><HR /></BLOCKQUOTE><BLOCKQUOTE><HR />dnoeth wrote:<BR /><P>This is probably not due to multiple rows, but bad data, periods require the start to be lower than the end:</P><PRE>select *
from tab
where STRT_TS >= END_TS</PRE><P>If this returns row you must decide how to eal with that.</P><P> </P><HR /></BLOCKQUOTE><P><BR /><BR />and i was able to fix it.</P>Thu, 10 Aug 2017 13:23:16 GMThttp://community.teradata.com/t5/Database/Split-time-between-two-time-stamps-into-hours/m-p/75094#M31247irfuhoney2017-08-10T13:23:16Z