I have a datetime field that is when an activity starts and an int field with active_time in minutes.

What I want to do is work out in minutes (and then hours /60) how much time the activity was spent during work hours (9-5) and time outside of those hours.

E.G. Data columns
Datetime: '2022-02-28 16:54:00.000 0000'.
Active_time in minutes: '20'

Desired output:
Activity time in work hours: '6'
Activity time out of work hours: '14'

Can anyone help?

Many thanks.

CodePudding user response：

I couldn't think of a way to approach this using built in date functions, so this is maybe not the prettiest solution, but the math is there. This converts the start_timestamp/active_time into minutes relative to your business hours

I subtracted 540 to essentially set the time 9:00AM to "minute 0" to make the numbers easier to work with. This makes 5:00PM "minute 480"

Then it's just a matter of subtracting times within and outside of your business hours.

set startdatetime = '2022-03-23 7:31:00'::timestamp_ntz;
set active_minutes = 125;
set business_start = 0;
set business_end = 480;

select 
-540   (hour($startdatetime) * 60   minute($startdatetime)) as start_minute,
start_minute   $active_minutes as end_minute,
-- End time (within business hours) - Start time (within business hours). Can't be less than 0
greatest(0, least(end_minute, $business_end) - greatest(start_minute, $business_start)) as minutes_during_business,
-- End - Start. With any "business minutes" ignored. first for pre-work minutes, then for post-work minutes
least(end_minute, $business_start) - least(start_minute, $business_start)
  greatest(end_minute, $business_end) - greatest(start_minute, $business_end) as minutes_outside_business,
minutes_during_business / 60 as hours_during_business,
minutes_outside_business / 60 as hours_outside_business;
;

This does not work well if your active minutes spans into business hours of the following day. That would take some extra handling.

You could also add on seconds, and convert all of the hardcoded numbers to seconds if you do want that extra granularity.

CodePudding user response：

SELECT start_time, minutes, time_pre_work, work_time, post_work_time
FROM (
    SELECT *
        ,timeadd('minute', minutes, start_time) as time_end
        ,date_trunc('day', start_time) as day
        ,timeadd('hour', 8, day) as workday_start
        ,timeadd('hour', 17, day) as workday_end
        ,timediff('minute', least(start_time, workday_start), workday_start) as time_pre_work
        ,timediff('minute', greatest(start_time, workday_start), least(workday_end, time_end)) as work_time
        ,timediff('minute', greatest(workday_end, workday_end), greatest(workday_end, time_end)) as post_work_time
    FROM VALUES
        ('2022-02-28 16:54:00.000'::timestamp, 20)
        t(start_time, minutes)
);

gives:

Within day clipping:

And not correctly bounding for multi-days, this data:

 FROM VALUES
        ('2022-02-28 16:54:00.000'::timestamp, 20),
        ('2022-02-28 7:54:00.000'::timestamp, 20),
        ('2022-02-28 6:54:00.000'::timestamp, 1000)
        t(start_time, minutes)

gives:

Across days with daily clipping:

WITH input_data as (
    SELECT * FROM VALUES
        ('2022-02-28 16:54:00.000'::timestamp, 20),
        ('2022-02-28 7:54:00.000'::timestamp, 20),
        ('2022-02-28 6:54:00.000'::timestamp, 3000)
        t(start_time, minutes)
), range as(
    SELECT row_number() over(order by null)-1 as rn
    FROM TABLE(generator(ROWCOUNT => 100))
), day_condition as (
    SELECT *
        ,timeadd('minute', minutes, start_time) as time_end
        ,date_trunc('day', dateadd('day', r.rn, start_time)) as r_day_start
        ,dateadd('day', 1, r_day_start ) as r_day_end
        ,greatest(r_day_start, start_time) as clip_start
        ,least(r_day_end, time_end) as clip_end
    -- insert logic for "which day is it and what hours it has here"
        ,timeadd('hour', 8, r_day_start) as workday_start
        ,timeadd('hour', 17, r_day_start) as workday_end
    FROM input_data i
    JOIN range r ON r.rn <= datediff(day, start_time, timeadd('minute', minutes, start_time))
) 
SELECT 
    start_time
    ,minutes
    ,r_day_start
    --,clip_start
    --,clip_end
    
    ,timediff('minute', least(clip_start, workday_start), workday_start) as time_pre_work
    ,timediff('minute', greatest(clip_start, workday_start), least(workday_end, clip_end)) as work_time
    ,timediff('minute', greatest(workday_end, workday_end), greatest(workday_end, clip_end)) as post_work_time
FROM day_condition
ORDER BY 1,3;