Working Hours Between two Timestamps

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Working Hours Between two Timestamps

Hi!!

Sorry for not contributing in this forum that much, I appretiate all the work that you all do for the community and most of the time, even being at a Middleground on SQL, I can do most of my reports only reading answers from other topics. Also, english is not my first language, so sorry for any mispelling.

 

The fact is that something came up and suddenly I'm the sole person in the department that knows (barely) how to write a script, and seemingly no one here can help with more difficult questions. To top it off, they've been giving me increasingly difficult requests, and for this one I know I can't even start.

 

So the question is "How to get the amount of working hours (preferably in seconds) from two timestamps?" We have a Calendar table, and I already made a script to get the working days off it:

 

CREATE MULTISET VOLATILE TABLE DIAS_HABILES, NO LOG AS (
SELECT SITE.SIT_SITE_ID,
TIM.TIM_DAY,
CASE WHEN TIM.TIM_WEEK_DAY in (2,3,4,5,6) AND HOL.SHP_HOLIDAY_DATE_ID is null THEN 1 ELSE 0 END WORKING_DAY,
SUM(WORKING_DAY) OVER (PARTITION BY SITE.SIT_SITE_ID ORDER BY TIM.TIM_DAY ROWS UNBOUNDED PRECEDING) AS ORDEN
FROM MyDaysTable TIM
CROSS JOIN MySitesTable SITE
LEFT JOIN MyHolidaysTable HOL
	ON SITE.SIT_SITE_ID = HOL.SIT_SITE_ID
	AND HOL.SHP_HOLIDAY_DATE_ID = TIM.TIM_DAY
WHERE TIM.TIM_DAY between '2018-01-01' and date 
)WITH DATA PRIMARY INDEX (SIT_SITE_ID, TIM_DAY) ON COMMIT PRESERVE ROWS;

 

 

But it is not enough. I know we have this topic: http://community.teradata.com/t5/Database/Calculate-business-days-hours-between-two-Timestamp-6-fiel... , but I could not even get started on it!

 

I'm really sorry to ask this for you guys, but I really need help on this one. Our working hours are 09-18 of the days mentioned above. Could any of you help me ellaborate this script?

Again, I really appretiate this community and love the product (especially combined with Alation).

 

Thanks all!

4 REPLIES
Teradata Employee

Re: Working Hours Between two Timestamps

Hi eduardofsjunior,

 

If your working hours are fixed, you can start with a solution like this :

(ts_end - ts_start) day(2) to second(4,0) - (ts_end (date) - ts_start (date)) * interval '15' hour

The first part is the basic interval between two timestamp, the second one is to remove 15 hours (9 - 0 + 24 - 18) per day from the first interval.

Once you have the interval, you can go with standard multiplication 

 

Teradata Employee

Re: Working Hours Between two Timestamps

I've refined the formulas, it seems ok :

 

with cte_data (id, ts_start, ts_end) as
(
select 1, timestamp '2018-09-05 09:00:00', timestamp '2018-09-05 18:00:00' from cte_dual union all
select 2, timestamp '2018-09-04 09:00:00', timestamp '2018-09-05 18:00:00' from cte_dual union all
select 3, timestamp '2018-09-04 16:00:00', timestamp '2018-09-05 10:00:00' from cte_dual union all
select 4, timestamp '2018-09-03 18:00:00', timestamp '2018-09-05 09:00:00' from cte_dual
)
  ,  cte_dual (dummy) as (select 'X')
select id, ts_start, ts_end
     , (ts_end - ts_start) day(2) to second(0) as base_interval
     , (ts_end (date)) - (ts_start (date))     as nb_days
     , nb_days * interval '15' hour            as nb_days_adjusted
     , base_interval - nb_days_adjusted        as working_interval
     , extract(day    from working_interval) * 60 * 60 * 24
     + extract(hour   from working_interval) * 60 * 60
     + extract(minute from working_interval) * 60
     + extract(second from working_interval)   as working_seconds
  from cte_data;

id  ts_start              ts_end               base_interval  nb_days  nb_days_adjusted  working_interval  working_seconds
--  --------------------  -------------------  -------------  -------  ----------------  ----------------  ---------------
1   2018-09-05 09:00:00   2018-09-05 18:00:00  0 09:00:00           0                 0  0 09:00:00                  32400
2   2018-09-04 09:00:00   2018-09-05 18:00:00  1 09:00:00           1                15  0 18:00:00                  64800
3   2018-09-04 16:00:00   2018-09-05 10:00:00  0 18:00:00           1                15  0 03:00:00                  10800
4   2018-09-03 18:00:00   2018-09-05 09:00:00  1 15:00:00           2                30  0 09:00:00                  32400

Re: Working Hours Between two Timestamps

Hi!!

First off, thank you very much for taking the time to respond me, and your solution seems very reliable, I understood the logic behind the SQL and I think I could apply that to my tables with very little handwork.

 

The sad thing is that, as I mentioned in the first post, we have to account for holidays and weekends to not be considered in the working interval. How would I be able to do this? Is there a way to extract the dates from the interval, compare with my holidays/weekends volatile table and discount 9 hours per day that is (or is not) in the table?

If my line of thinking is confuse, please ask away so I can clarify the situation.


Thanks again!!

Teradata Employee

Re: Working Hours Between two Timestamps


@eduardofsjunior wrote:

we have to account for holidays and weekends to not be considered in the working interval. How would I be able to do this? Is there a way to extract the dates from the interval, compare with my holidays/weekends volatile table and discount 9 hours per day that is (or is not) in the table?


Exactly like you said. Just do a join (or a scalar query, depending on the volumes) to count the number of holidays / weekend between both timestamp, multiply 9 hours to this number and substract it from the working interval :

select sum(1 - working_day) * interval '9' hour as not_worked_hours
  from dias_habiles
 where tim_day between ts_start (date) and ts_end (date)