Home > Blockchain >  Python Datetime calculation
Python Datetime calculation

Time：05-29

I would like to create a function with python, this is the calculation, if end time of a shift is after 20:00 and between 06:00 it has to create me an extra 25% in minutes for each hour passed after 20:00. Any suggestions?

CodePudding user response：

UPDATED:

Here is a way to do what I believe your question asks:

from datetime import datetime, timedelta
def getHours(startTime, endTime, extraFraction):
    if endTime < startTime:
        raise ValueError(f'endTime {endTime} is before startTime {startTime}')
    startDateStr = startTime.strftime("%Y-%m-%d")
    bonusStartTime = datetime.strptime(startDateStr   " "   "20:00:00", "%Y-%m-%d %H:%M:%S")
    prevBonusEndTime = datetime.strptime(startTime.strftime("%Y-%m-%d")   " "   "06:00:00", "%Y-%m-%d %H:%M:%S")
    bonusEndTime = prevBonusEndTime   timedelta(days=1)
    bonusPeriod = timedelta(days=0)
    duration = endTime - startTime
    hours = duration.total_seconds() // 3600
    if hours > 24:
        fullDays = hours // 24
        bonusPeriod  = fullDays * (bonusEndTime - bonusStartTime)
        endTime -= timedelta(days=fullDays)
    if startTime < prevBonusEndTime:
        bonusPeriod  = prevBonusEndTime - startTime
    if endTime < prevBonusEndTime:
        bonusPeriod -= prevBonusEndTime - endTime
    if startTime > bonusStartTime:
        bonusPeriod -= startTime - bonusStartTime
    if endTime > bonusStartTime:
        bonusPeriod  = min(endTime, bonusEndTime) - bonusStartTime
    delta = duration   bonusPeriod * extraFraction
    return delta

Explanation:

  • confirm startTime is before endTime, otherwise raise an exception
  • set the following:
    • prevBonusStartTime as 20:00 on the day before startTime
    • bonusStartTime as 20:00 on the day of startTime
    • bonusEndTime as 06:00 on the day after startTime
  • if endTime is more than 24 hours after startTime, record this in duration and bonusPeriod and rewind endTime by the number of full days (24-hour periods) by which it exceeds startTime
  • add or subtract to bonusPeriod by the number of hours (in addition to any calculated above) of overlap between startTime, endTime and the intervals 00:00, prevBonusEndTime and/or bonusStartTime, bonusEndTime.

Test code:


def testing(start, end):
    print(f'start {start}, end {end}, actual hours {getHours(start, end, 0)}, effective hours {getHours(start, end, 0.25)}')

startTime = datetime.strptime("2022-05-26 06:00:00", "%Y-%m-%d %H:%M:%S")
endTime = startTime
for h in range(0, 48, 3):
    testing(startTime, endTime   timedelta(hours=h))
endTime  = timedelta(hours=48)
for h in range(0, 48, 3):
    testing(startTime   timedelta(hours=h), endTime)

Output:

start 2022-05-26 06:00:00, end 2022-05-26 06:00:00, actual hours 0:00:00, effective hours 0:00:00
start 2022-05-26 06:00:00, end 2022-05-26 09:00:00, actual hours 3:00:00, effective hours 3:00:00
start 2022-05-26 06:00:00, end 2022-05-26 12:00:00, actual hours 6:00:00, effective hours 6:00:00
start 2022-05-26 06:00:00, end 2022-05-26 15:00:00, actual hours 9:00:00, effective hours 9:00:00
start 2022-05-26 06:00:00, end 2022-05-26 18:00:00, actual hours 12:00:00, effective hours 12:00:00
start 2022-05-26 06:00:00, end 2022-05-26 21:00:00, actual hours 15:00:00, effective hours 15:15:00
start 2022-05-26 06:00:00, end 2022-05-27 00:00:00, actual hours 18:00:00, effective hours 19:00:00
start 2022-05-26 06:00:00, end 2022-05-27 03:00:00, actual hours 21:00:00, effective hours 22:45:00
start 2022-05-26 06:00:00, end 2022-05-27 06:00:00, actual hours 1 day, 0:00:00, effective hours 1 day, 2:30:00
start 2022-05-26 06:00:00, end 2022-05-27 09:00:00, actual hours 1 day, 3:00:00, effective hours 1 day, 5:30:00
start 2022-05-26 06:00:00, end 2022-05-27 12:00:00, actual hours 1 day, 6:00:00, effective hours 1 day, 8:30:00
start 2022-05-26 06:00:00, end 2022-05-27 15:00:00, actual hours 1 day, 9:00:00, effective hours 1 day, 11:30:00
start 2022-05-26 06:00:00, end 2022-05-27 18:00:00, actual hours 1 day, 12:00:00, effective hours 1 day, 14:30:00
start 2022-05-26 06:00:00, end 2022-05-27 21:00:00, actual hours 1 day, 15:00:00, effective hours 1 day, 17:45:00
start 2022-05-26 06:00:00, end 2022-05-28 00:00:00, actual hours 1 day, 18:00:00, effective hours 1 day, 21:30:00
start 2022-05-26 06:00:00, end 2022-05-28 03:00:00, actual hours 1 day, 21:00:00, effective hours 2 days, 1:15:00
start 2022-05-26 06:00:00, end 2022-05-28 06:00:00, actual hours 2 days, 0:00:00, effective hours 2 days, 5:00:00
start 2022-05-26 09:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 21:00:00, effective hours 2 days, 2:00:00
start 2022-05-26 12:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 18:00:00, effective hours 1 day, 23:00:00
start 2022-05-26 15:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 15:00:00, effective hours 1 day, 20:00:00
start 2022-05-26 18:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 12:00:00, effective hours 1 day, 17:00:00
start 2022-05-26 21:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 9:00:00, effective hours 1 day, 13:45:00
start 2022-05-27 00:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 6:00:00, effective hours 1 day, 10:00:00
start 2022-05-27 03:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 3:00:00, effective hours 1 day, 6:15:00
start 2022-05-27 06:00:00, end 2022-05-28 06:00:00, actual hours 1 day, 0:00:00, effective hours 1 day, 2:30:00
start 2022-05-27 09:00:00, end 2022-05-28 06:00:00, actual hours 21:00:00, effective hours 23:30:00
start 2022-05-27 12:00:00, end 2022-05-28 06:00:00, actual hours 18:00:00, effective hours 20:30:00
start 2022-05-27 15:00:00, end 2022-05-28 06:00:00, actual hours 15:00:00, effective hours 17:30:00
start 2022-05-27 18:00:00, end 2022-05-28 06:00:00, actual hours 12:00:00, effective hours 14:30:00
start 2022-05-27 21:00:00, end 2022-05-28 06:00:00, actual hours 9:00:00, effective hours 11:15:00
start 2022-05-28 00:00:00, end 2022-05-28 06:00:00, actual hours 6:00:00, effective hours 7:30:00
start 2022-05-28 03:00:00, end 2022-05-28 06:00:00, actual hours 3:00:00, effective hours 3:45:00

UPDATE #2:

Here is slightly modified code that outputs regular hours, bonus hours (i.e., hours in the bonus window from 20:00 to 06:00) and extra hours (25% * bonus hours):

from datetime import datetime, timedelta

def getRegularAndBonusHours(startTime, endTime):
    if endTime < startTime:
        raise ValueError(f'endTime {endTime} is before startTime {startTime}')
    startDateStr = startTime.strftime("%Y-%m-%d")
    bonusStartTime = datetime.strptime(startDateStr   " "   "20:00:00", "%Y-%m-%d %H:%M:%S")
    prevBonusEndTime = datetime.strptime(startTime.strftime("%Y-%m-%d")   " "   "06:00:00", "%Y-%m-%d %H:%M:%S")
    bonusEndTime = prevBonusEndTime   timedelta(days=1)
    bonusPeriod = timedelta(days=0)
    duration = endTime - startTime
    hours = duration.total_seconds() // 3600
    if hours > 24:
        fullDays = hours // 24
        bonusPeriod  = fullDays * (bonusEndTime - bonusStartTime)
        endTime -= timedelta(days=fullDays)
    if startTime < prevBonusEndTime:
        bonusPeriod  = prevBonusEndTime - startTime
    if endTime < prevBonusEndTime:
        bonusPeriod -= prevBonusEndTime - endTime
    if startTime > bonusStartTime:
        bonusPeriod -= startTime - bonusStartTime
    if endTime > bonusStartTime:
        bonusPeriod  = min(endTime, bonusEndTime) - bonusStartTime
    return duration, bonusPeriod

def getHours(startTime, endTime, extraFraction):
    duration, bonusPeriod = getRegularAndBonusHours(startTime, endTime)
    delta = duration   bonusPeriod * extraFraction
    return delta

def testing(start, end):
    duration, bonusPeriod = getRegularAndBonusHours(start, end)
    def getHoursRoundedUp(delta):
        return delta.days * 24   delta.seconds // 3600   (1 if delta.seconds % 3600 else 0)
        
    regularHours, bonusHours = getHoursRoundedUp(duration), getHoursRoundedUp(bonusPeriod)
    print(f'start {start}, end {end}, regular {regularHours}, bonus {bonusHours}, extra {0.25 * bonusHours}')

startTime = datetime.strptime("2022-05-26 06:00:00", "%Y-%m-%d %H:%M:%S")
endTime = startTime
for h in range(0, 48, 3):
    testing(startTime, endTime   timedelta(hours=h))
endTime  = timedelta(hours=48)
for h in range(0, 48, 3):
    testing(startTime   timedelta(hours=h), endTime)

Output:

start 2022-05-26 06:00:00, end 2022-05-26 06:00:00, regular 0, bonus 0, extra 0.0
start 2022-05-26 06:00:00, end 2022-05-26 09:00:00, regular 3, bonus 0, extra 0.0
start 2022-05-26 06:00:00, end 2022-05-26 12:00:00, regular 6, bonus 0, extra 0.0
start 2022-05-26 06:00:00, end 2022-05-26 15:00:00, regular 9, bonus 0, extra 0.0
start 2022-05-26 06:00:00, end 2022-05-26 18:00:00, regular 12, bonus 0, extra 0.0
start 2022-05-26 06:00:00, end 2022-05-26 21:00:00, regular 15, bonus 1, extra 0.25
start 2022-05-26 06:00:00, end 2022-05-27 00:00:00, regular 18, bonus 4, extra 1.0
start 2022-05-26 06:00:00, end 2022-05-27 03:00:00, regular 21, bonus 7, extra 1.75
start 2022-05-26 06:00:00, end 2022-05-27 06:00:00, regular 24, bonus 10, extra 2.5
start 2022-05-26 06:00:00, end 2022-05-27 09:00:00, regular 27, bonus 10, extra 2.5
start 2022-05-26 06:00:00, end 2022-05-27 12:00:00, regular 30, bonus 10, extra 2.5
start 2022-05-26 06:00:00, end 2022-05-27 15:00:00, regular 33, bonus 10, extra 2.5
start 2022-05-26 06:00:00, end 2022-05-27 18:00:00, regular 36, bonus 10, extra 2.5
start 2022-05-26 06:00:00, end 2022-05-27 21:00:00, regular 39, bonus 11, extra 2.75
start 2022-05-26 06:00:00, end 2022-05-28 00:00:00, regular 42, bonus 14, extra 3.5
start 2022-05-26 06:00:00, end 2022-05-28 03:00:00, regular 45, bonus 17, extra 4.25
start 2022-05-26 06:00:00, end 2022-05-28 06:00:00, regular 48, bonus 20, extra 5.0
start 2022-05-26 09:00:00, end 2022-05-28 06:00:00, regular 45, bonus 20, extra 5.0
start 2022-05-26 12:00:00, end 2022-05-28 06:00:00, regular 42, bonus 20, extra 5.0
start 2022-05-26 15:00:00, end 2022-05-28 06:00:00, regular 39, bonus 20, extra 5.0
start 2022-05-26 18:00:00, end 2022-05-28 06:00:00, regular 36, bonus 20, extra 5.0
start 2022-05-26 21:00:00, end 2022-05-28 06:00:00, regular 33, bonus 19, extra 4.75
start 2022-05-27 00:00:00, end 2022-05-28 06:00:00, regular 30, bonus 16, extra 4.0
start 2022-05-27 03:00:00, end 2022-05-28 06:00:00, regular 27, bonus 13, extra 3.25
start 2022-05-27 06:00:00, end 2022-05-28 06:00:00, regular 24, bonus 10, extra 2.5
start 2022-05-27 09:00:00, end 2022-05-28 06:00:00, regular 21, bonus 10, extra 2.5
start 2022-05-27 12:00:00, end 2022-05-28 06:00:00, regular 18, bonus 10, extra 2.5
start 2022-05-27 15:00:00, end 2022-05-28 06:00:00, regular 15, bonus 10, extra 2.5
start 2022-05-27 18:00:00, end 2022-05-28 06:00:00, regular 12, bonus 10, extra 2.5
start 2022-05-27 21:00:00, end 2022-05-28 06:00:00, regular 9, bonus 9, extra 2.25
start 2022-05-28 00:00:00, end 2022-05-28 06:00:00, regular 6, bonus 6, extra 1.5
start 2022-05-28 03:00:00, end 2022-05-28 06:00:00, regular 3, bonus 3, extra 0.75

UPDATE #3

Latest clarification from OP in a comment indicates:

  • A need to update in excel the allowances received in case of night work
  • The goal in the excel sheet is to separately enter start time, end time, working time (without supplement), and night work supplement (25% from 20:00 to 06:) for each hour started for night work.

Here is updated code to create the required data result, and optionally to use a pandas dataframe to put this into an Excel file. Test inputs are used to explore a range of start and end times, including partial hours:

from datetime import datetime, timedelta

def getRegularAndBonusHours(startTime, endTime):
    if endTime < startTime:
        raise ValueError(f'endTime {endTime} is before startTime {startTime}')
    startDateStr = startTime.strftime("%Y-%m-%d")
    bonusStartTime = datetime.strptime(startDateStr   " "   "20:00:00", "%Y-%m-%d %H:%M:%S")
    prevBonusEndTime = datetime.strptime(startTime.strftime("%Y-%m-%d")   " "   "06:00:00", "%Y-%m-%d %H:%M:%S")
    bonusEndTime = prevBonusEndTime   timedelta(days=1)
    bonusPeriod = timedelta(days=0)
    duration = endTime - startTime
    hours = duration.total_seconds() // 3600
    if hours > 24:
        fullDays = hours // 24
        bonusPeriod  = fullDays * (bonusEndTime - bonusStartTime)
        endTime -= timedelta(days=fullDays)
    if startTime < prevBonusEndTime:
        bonusPeriod  = prevBonusEndTime - startTime
    if endTime < prevBonusEndTime:
        bonusPeriod -= prevBonusEndTime - endTime
    if startTime > bonusStartTime:
        bonusPeriod -= startTime - bonusStartTime
    if endTime > bonusStartTime:
        bonusPeriod  = min(endTime, bonusEndTime) - bonusStartTime
    return duration, bonusPeriod

def testing(start, end):
    duration, bonusPeriod = getRegularAndBonusHours(start, end)
    def getHoursFromDelta(delta, roundUp=False):
        return delta.days * 24   (delta.seconds // 3600   (1 if delta.seconds % 3600 else 0)) if roundUp else (delta.seconds / 3600)
        
    fullHours, bonusHours = getHoursFromDelta(duration   bonusPeriod), getHoursFromDelta(bonusPeriod, True)
    return start, end, fullHours, bonusHours * 0.25

# calculate test results
results = []
startTime = datetime.strptime("2022-05-26 06:00:00", "%Y-%m-%d %H:%M:%S")
endTime = startTime
for halfHours in range(0, 2 * 48, 5):
    results.append(testing(startTime, endTime   timedelta(hours=halfHours / 2)))
endTime  = timedelta(hours=48)
for halfHours in range(0, 2 * 48, 5):
    results.append(testing(startTime   timedelta(hours=halfHours / 2), endTime))

# print results
headings = ['Start Time', 'End Time', 'Working Hours', '25% of Supplemental Hours Started']
[print(f'{x:30}', end='') for x in headings]
[[print(f'{f"{x}":30}', end='') for x in row] for row in results if print() or True]
print()

# OPTIONAL: save results in pandas dataframe and save as Excel file
import pandas as pd
df = pd.DataFrame(results, columns=headings)
print(df)
with pd.ExcelWriter('TestTimesheet.xlsx') as writer:
    df.to_excel(writer, index=None, sheet_name='Timesheet')
    ws = writer.sheets['Timesheet']
    for column in df:
        column_length = max(df[column].astype(str).map(len).max(), len(column))
        col_idx = df.columns.get_loc(column)
        ws.column_dimensions[chr(ord('A')   col_idx)].width = column_length

Output:

            Start Time            End Time  Working Hours  25% of Supplemental Hours Started
0  2022-05-26 06:00:00 2022-05-26 06:00:00            0.0                               0.00
1  2022-05-26 06:00:00 2022-05-26 08:30:00            2.5                               0.00
2  2022-05-26 06:00:00 2022-05-26 11:00:00            5.0                               0.00
3  2022-05-26 06:00:00 2022-05-26 13:30:00            7.5                               0.00
4  2022-05-26 06:00:00 2022-05-26 16:00:00           10.0                               0.00
5  2022-05-26 06:00:00 2022-05-26 18:30:00           12.5                               0.00
6  2022-05-26 06:00:00 2022-05-26 21:00:00           16.0                               0.25
7  2022-05-26 06:00:00 2022-05-26 23:30:00           21.0                               1.00
8  2022-05-26 06:00:00 2022-05-27 02:00:00            2.0                               1.50
9  2022-05-26 06:00:00 2022-05-27 04:30:00            7.0                               2.25
10 2022-05-26 06:00:00 2022-05-27 07:00:00           11.0                               2.50
11 2022-05-26 06:00:00 2022-05-27 09:30:00           13.5                               2.50
12 2022-05-26 06:00:00 2022-05-27 12:00:00           16.0                               2.50
13 2022-05-26 06:00:00 2022-05-27 14:30:00           18.5                               2.50
14 2022-05-26 06:00:00 2022-05-27 17:00:00           21.0                               2.50
15 2022-05-26 06:00:00 2022-05-27 19:30:00           23.5                               2.50
16 2022-05-26 06:00:00 2022-05-27 22:00:00            4.0                               3.00
17 2022-05-26 06:00:00 2022-05-28 00:30:00            9.0                               3.75
18 2022-05-26 06:00:00 2022-05-28 03:00:00           14.0                               4.25
19 2022-05-26 06:00:00 2022-05-28 05:30:00           19.0                               5.00
20 2022-05-26 06:00:00 2022-05-28 06:00:00           20.0                               5.00
21 2022-05-26 08:30:00 2022-05-28 06:00:00           17.5                               5.00
22 2022-05-26 11:00:00 2022-05-28 06:00:00           15.0                               5.00
23 2022-05-26 13:30:00 2022-05-28 06:00:00           12.5                               5.00
24 2022-05-26 16:00:00 2022-05-28 06:00:00           10.0                               5.00
25 2022-05-26 18:30:00 2022-05-28 06:00:00            7.5                               5.00
26 2022-05-26 21:00:00 2022-05-28 06:00:00            4.0                               4.75
27 2022-05-26 23:30:00 2022-05-28 06:00:00           23.0                               4.25
28 2022-05-27 02:00:00 2022-05-28 06:00:00           18.0                               3.50
29 2022-05-27 04:30:00 2022-05-28 06:00:00           13.0                               3.00
30 2022-05-27 07:00:00 2022-05-28 06:00:00            9.0                               2.50
31 2022-05-27 09:30:00 2022-05-28 06:00:00            6.5                               2.50
32 2022-05-27 12:00:00 2022-05-28 06:00:00            4.0                               2.50
33 2022-05-27 14:30:00 2022-05-28 06:00:00            1.5                               2.50
34 2022-05-27 17:00:00 2022-05-28 06:00:00           23.0                               2.50
35 2022-05-27 19:30:00 2022-05-28 06:00:00           20.5                               2.50
36 2022-05-27 22:00:00 2022-05-28 06:00:00           16.0                               2.00
37 2022-05-28 00:30:00 2022-05-28 06:00:00           11.0                               1.50
38 2022-05-28 03:00:00 2022-05-28 06:00:00            6.0                               0.75
39 2022-05-28 05:30:00 2022-05-28 06:00:00            1.0                               0.25

Page link：https//www.codepudding.com/Blockchain/423436.html

Prev:Spring Boot session management - combined solution PostgreSQL Redis
Next:Footer items not centered on small sizes, but they are on bigger screens
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding