Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

determining work hours between 2 dates

by kosie99 (Acolyte)
on Jun 12, 2006 at 18:40 UTC ( #554871=perlquestion: print w/replies, xml ) Need Help??

kosie99 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I need to develop a perl sub that will return the number of work hours between 2 supplied dates. So I was thinking that i will need to loop through each day and then determine if it is a weekday, or a holiday (I will have an array of holidays), or if DST comes into play. Now, I am not too sure how to tackle this. How will I be able to loop through the days, etc? Anybody any ideas on how to start? Thanks! Kosie

Replies are listed 'Best First'.
Re: determining work hours between 2 dates
by jpeg (Chaplain) on Jun 12, 2006 at 19:27 UTC
    Or the oft-overlooked Date::Business has a diffb function which does exactly what you want.
    --
    jpg
      I just had to solve this exact problem and Date::Business worked perfectly. See my test script below:
      use strict; use warnings; use Business::Hours; use Time::Local; my $timeout_period_hours = 12; my @tests = ( { start_date => "2006-06-05 00:00:00", + # Monday end_date => "2006-06-05 00:59:59", + # Monday comment => "1 hour - in a business d +ay" }, { start_date => "2006-06-05 00:00:00", + # Monday end_date => "2006-06-05 08:59:59", + # Monday comment => "9 hours - in a business +day" }, { start_date => "2006-06-05 00:00:00", + # Monday end_date => "2006-06-05 11:59:59", + # Monday comment => "12 hours - in a business + day" }, { start_date => "2006-06-05 00:00:00", + # Monday end_date => "2006-06-05 12:59:59", + # Monday comment => "13 hours - in a business + day" }, { start_date => "2006-06-05 00:00:00", + # Monday end_date => "2006-06-05 23:59:59", + # Monday comment => "1 day - 1 business day" +}, { start_date => "2006-06-04 00:00:00", + # Sun end_date => "2006-06-05 23:59:59", + # Monday comment => "2 days - 1 business day" + }, { start_date => "2006-06-03 00:00:00", + # Sat end_date => "2006-06-05 23:59:59", + # Monday comment => "3 days - 1 business day" + }, { start_date => "2006-06-02 00:00:00", + # Friday end_date => "2006-06-05 23:59:59", + # Monday comment => "4 days - 2 business days +" }, { start_date => "2006-06-01 00:00:00", + # Thurs end_date => "2006-06-05 23:59:59", + # Monday comment => "5 days - 3 business days +" }, { start_date => "2006-05-31 00:00:00", + # Wed end_date => "2006-06-05 23:59:59", + # Monday comment => "6 days - 4 business days +" }, { start_date => "2006-05-30 00:00:00", + # Tuesday end_date => "2006-06-05 23:59:59", + # Monday comment => "7 days - 5 business days +" }, { start_date => "2006-05-29 00:00:00", + # Monday end_date => "2006-06-05 23:59:59", + # Monday comment => "8 days - 6 business days +" }, { start_date => "2006-05-28 00:00:00", + # Sunday end_date => "2006-06-05 23:59:59", + # Monday comment => "9 days - 6 business days +" }, { start_date => "2006-05-27 00:00:00", + # Saturday end_date => "2006-06-05 23:59:59", + # Monday comment => "10 days - 6 business day +s" }, { start_date => "2006-05-26 00:00:00", + # Friday end_date => "2006-06-05 23:59:59", + # Monday comment => "11 days - 7 business day +s" }, { start_date => "2006-05-25 00:00:00", + # Thursday end_date => "2006-06-05 23:59:59", + # Monday comment => "12 days - 8 business day +s" }, { start_date => "2006-06-03 00:00:00", + # Saturday end_date => "2006-06-04 23:59:59", + # Sunday comment => "2 non-business days" }, { start_date => "2006-06-04 00:00:00", + # Sunday end_date => "2006-06-05 01:59:59", + # Monday comment => "2 hours - 2 non-business + and 1 business days" }, { start_date => "2006-06-03 00:00:00", + # Saturday end_date => "2006-06-05 01:59:59", + # Monday comment => "2 hours - 2 non-business + and 1 business days" }, # THESE ARE LARGE TIME SPAN TESTS { start_date => "2006-01-03 00:00:00", + # Tuesday end_date => "2006-06-05 01:59:59", + # Monday comment => "? hours - 2 non-business + and 1 business days" }, { start_date => "2005-01-03 00:00:00", + # Tuesday end_date => "2006-06-05 01:59:59", + # Monday comment => "? hours - 2 non-business + and 1 business days" }, ); my $bus_hours = Business::Hours->new(); $bus_hours->business_hours( 0 => { Name => 'Sunday', Start => undef +, End => undef}, 1 => { Name => 'Monday', Start => '00:0 +0', End => '23:59'}, 2 => { Name => 'Tuesday', Start => '00:0 +0', End => '23:59'}, 3 => { Name => 'Wednesday', Start => '00:0 +0', End => '23:59'}, 4 => { Name => 'Thursday', Start => '00:0 +0', End => '23:59'}, 5 => { Name => 'Friday', Start => '00:0 +0', End => '23:59'}, 6 => { Name => 'Saturday', Start => undef +, End => undef} ); my $test_counter = 0; foreach (@tests) { my $test = $_; $test_counter++; my $comment = $test->{comment} || ''; my $end_date = $test->{end_date}; my @end_date = split(/\D/, $end_date); my $end_time = timelocal($end_date[5], $end_date[4], $end_ +date[3], $end_date[2], ($end_date[1] - 1), $end_date[0]); my $start_date = $test->{start_date}; my @start_date = split(/\D/, $start_date); my $start_time = timelocal($start_date[5], $start_date[4], $ +start_date[3], $start_date[2], ($start_date[1] - 1), $start_date[0]); my $result = $bus_hours->for_timespan(Start => $start_ti +me, End => $end_time); #my $elements = $result->elements(); #my $elements_count = $result->cardinality(); #my $seconds = scalar(@$elements); my $seconds = $result->cardinality(); my $hours = ($seconds / 60 / 60); my $days = ($hours / 24); #$hours = sprintf("%d", $hours); #$days = sprintf("%d", $days); #print "TEST[$test_counter]: [$start_date][$end_date]\n[$comment]\n +DIFFERENCE SECONDS/HOURS: [$seconds][$hours]\n\n"; print "TEST[$test_counter]: [$comment]\nDATES: [$start_date] TO [$e +nd_date]\nBUSINESS SECONDS/HOURS/DAYS: [$seconds][$hours][$days]\n\n" +; } print "Done\n";
Re: determining work hours between 2 dates
by ambrus (Abbot) on Jun 12, 2006 at 21:22 UTC

    Date::Manip is a very nice module and has "business" mode when only work hours are counted.

Re: determining work hours between 2 dates
by InfiniteLoop (Hermit) on Jun 12, 2006 at 19:01 UTC
    Try using Date::Range. You could loop thru the range, where each date is represented by a Time::Piece object, and discard the holidays, while retaining only working days.
Re: determining work hours between 2 dates
by sh1tn (Priest) on Jun 12, 2006 at 18:51 UTC
Re: determining work hours between 2 dates
by monarch (Priest) on Jun 12, 2006 at 19:22 UTC
    DST shouldn't be an issue unless you are working across timezones, e.g. if you wanted to know the number of work hours between midnight in Los Angeles and midnight in Sydney. If you are calculating work hours between any two business days then you will always use the standard 7.5 or 8 hours (depending on your business) as daylight savings takes effect at 3am which doesn't affect normal business hours.

    In other words: think about your problem more carefully..

      I can think of any number of businesses that work 24/24 7/7, so DST may be an issue.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

        Amen and ++, CountZero. Been there, done that. Hated "fall back" when our kindly and generous employer sought to have us work the extra hour, sans pay (Same employer docked us an hour in the spring).
Re: determining work hours between 2 dates
by poqui (Deacon) on Jun 13, 2006 at 03:31 UTC
    The way we count workdays between 2 dates in data warehousing is to use a dimension table.

    The Date Dimension will contain a row for every day in the calendar for a period of years; as far into the past as you are likely to care about, and at least 2 years into the future.
    Each day is keyed with the date in date format (or you could use epochal date). There will be a column with a flag for Holiday, a flag for Weekday, and a flag for Workday.

    We also have an aggregated column of Week_work_days_to_date, Month_work_days_to_date, Quarter..., Year..., Fiscal_year...; mainly because those are the most requested time spans.
    If the time span you are interested in is not one of the standards, then you merely sum the the Workday flag for every day between your start and end dates.
Re: determining work hours between 2 dates
by kosie99 (Acolyte) on Jun 12, 2006 at 20:12 UTC
    There is one complication though: The 2 dates have a time component as well, so the first day can start any time and the end date too. So, I will need to calculate the HH:MM:SS for the first and last days as well. Kosie
      That's easy enough, though: deal with the first and last days as special cases (with another special case if they are the same day), and use a standard tool for the ones inbetween.
      >> The 2 dates have a time component as well, so the first day
      >> can start any time and the end date too. So, I will need to
      >> calculate the HH:MM:SS for the first and last days as well.

      That's actually already taken into account in my test script above.
      It does the calculations in seconds and then converts to hours.

      The hard part for you may be dealing with holidays.
Re: determining work hours between 2 dates
by mvandenb (Beadle) on Jun 13, 2006 at 13:19 UTC
    I have a similar requirement in the application I manage. There I have the added complexity that work hours are not necessarily the same for all employees (part-timers, weekend duty, ...), weekends don't fall on the same days in all countries (eg. Middle East), holidays are not the same in all countries, etc ...
    Just wanted to mention this, in case you overlooked.
      Thanks for all the input people! I am going to give it a try with the Date::Calendar module using the delta_workdays method. It handles holidays, etc. If I get it to work I will post however I have done it here for scrutiny from you all :) Kosie
        Ok, I finally decided to do it without any Date:: modules. Here is a working version of my script and I admit some places look a bit crude ;)

Re: determining work hours between 2 dates
by mojotoad (Monsignor) on Jun 13, 2006 at 20:35 UTC
    Here's one way using DateTime.pm and DateTime::Event::Cron, though it does not take into account holidays and assumes Mon-Fri 8 hrs per day. <shrug> :
    use strict; use warnings; use DateTime; my $start_date = DateTime->now(); my $end_date = $start_date + DateTime::Duration->new(months => 2); print $start_date->datetime, "\n", $end_date->datetime, "\n\n"; use DateTime::Event::Cron; my $crontab = '0 0 * * 1-5'; my $set = DateTime::Event::Cron->from_cron( cron => $crontab, start => $start_date, before => $end_date # to include $end_date, use 'end' rather than 'before' ); my @weekdays = $set->as_list; print scalar @weekdays, " weekdays : ", scalar @weekdays * 8, " work h +ours\n";

    Cheers,
    Matt

Re: determining work hours between 2 dates
by kosie99 (Acolyte) on Jun 23, 2006 at 11:10 UTC
    Ok, I finally decided to do it without any Date:: modules. Here is a working version of my script and I admit some places look a bit crude ;)
    #!/usr/bin/perl # # use strict; use Time::Local; use Text::CSV; my $debug = 0; my $infile; while (@ARGV) { $_ = shift; /(.*\.csv$)/ && do {$infile = $1; next}; /^-d/ && do {$debug++; next}; } my %holidays = { "20060101" => 1, "20060317" => 1, "20060414" => 1, "20060417" => 1, "20060501" => 1, "20060605" => 1, "20060807" => 1, "20061030" => 1, "20061225" => 1, "20061226" => 1 }; #my @dates = ('2006/06/12 09:00:00|2006/06/16 13:00:00'); open(IF,"$infile") || die "cannot open $infile\n"; while (my $line = <IF>) { $line =~ /^FAULT/ && next; my $csv = Text::CSV->new; $csv->parse($line) || die "Could not parse line:$line:$csv->error_in +put"; my ($ref,$start,$end) = $csv->fields; my $datepair = "$start" . "|" . "$end"; my $work_seconds = &get_work_seconds($datepair); print "$ref,$work_seconds\n"; } close(IF); ######################## sub get_work_seconds { my $datepair = shift; print "$datepair\n" if $debug; $datepair =~ /(\d{4})\/(\d{2})\/(\d{2}) (\d{2}):(\d{2}):(\d{2})\|(\d +{4})\/(\d{2})\/(\d{2}) (\d{2}):(\d{2}):(\d{2})/; my ($y1,$m1,$d1,$h1,$min1,$s1) = ($1,$2,$3,$4,$5,$6); my ($y2,$m2,$d2,$h2,$min2,$s2) = ($7,$8,$9,$10,$11,$12); my $yyyymmdd1 = $y1 . $m1 . $d1; my $yyyymmdd2 = $y2 . $m2 . $d2; my ($day_start1,$day_end1) = &day_ctime_range($yyyymmdd1); my ($day_start2,$day_end2) = &day_ctime_range($yyyymmdd2); my $date1 = timelocal($s1,$min1,$h1,$d1,$m1-1,$y1); my $date2 = timelocal($s2,$min2,$h2,$d2,$m2-1,$y2); if ($date1 < $day_start1) { $date1 = $day_start1; } if ($date1 > $day_end1) { $date1 = $day_end1; } if ($date2 < $day_start2) { $date2 = $day_start2; } if ($date2 > $day_end2) { $date2 = $day_end2; } my $date1_secs = $day_end1 - $date1; my $date2_secs = $date2 - $day_start2; if (&non_workday($date1)) { $date1_secs = 0; } if (&non_workday($date2)) { $date2_secs = 0; } print "date1_secs=$date1_secs\n" if $debug; print "date2_secs=$date2_secs\n" if $debug; my $days = 0; $date1 += 86400; while ($date1 < $day_start2) { my $nwday = &non_workday($date1); if ($nwday == 0) { $days++; } $date1 += 86400; } print "NUMBER_DAYS=$days\n" if $debug; my $seconds = ((8.5 * 60 * 60) * $days) + $date1_secs + $date2_secs; print ("((8.5 * 60 * 60) * $days) + $date1_secs + $date2_secs = $sec +onds\n") if $debug; return $seconds; } ######################## sub day_ctime_range { #for a given YYMMDD return start and end ctime my $today = shift(); $today =~ /^(\d\d\d\d)(\d\d)(\d\d)$/; my ($yyyy,$mm,$dd) = ($1,$2,$3); my $start = timelocal(0,30,8,$dd,$mm-1,$yyyy-1900); my $end = timelocal(0,0,17,$dd,$mm-1,$yyyy-1900); return ($start,$end); } ######################################## sub non_workday { my ($some_time) = shift; my $non_workday = 0; # check if a day is a holiday my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = loc +altime($some_time); my $yyyy = $year+1900; my $mm = $mon+1; my $yyyymmdd= sprintf ("%4d%02d%02d", $yyyy, $mm, $mday); $non_workday = 1 if exists $holidays{$yyyymmdd}; #weekend? if ($wday == 0 || $wday == 6) { $non_workday = 1; } return ($non_workday); } ########################################
Re: determining work hours between 2 dates
by kosie99 (Acolyte) on Jul 26, 2006 at 10:28 UTC
    Ok, fixed a bug, new script:
    #!/usr/bin/perl # # CORRECT VERSION # use strict; use Time::Local; use Text::CSV; my $debug = 0; my $infile; while (@ARGV) { $_ = shift; /(.*\.csv$)/ && do {$infile = $1; next}; /^-d/ && do {$debug++; next}; } my %holidays = { "20060101" => 1, "20060317" => 1, "20060414" => 1, "20060417" => 1, "20060501" => 1, "20060605" => 1, "20060807" => 1, "20061030" => 1, "20061225" => 1, "20061226" => 1 }; open(IF,"$infile") || die "cannot open $infile\n"; while (my $line = <IF>) { $line =~ /^FAULT/ && next; my $csv = Text::CSV->new; $csv->parse($line) || die "Could not parse line:$line:$csv->error_in +put"; my ($ref,$a,$b,$c,$d,$start,$end) = $csv->fields; my $datepair = "$start" . "|" . "$end"; my $work_seconds = &get_work_seconds($datepair); print "$ref,$work_seconds\n"; } close(IF); ######################## sub get_work_seconds { my $datepair = shift; print "$datepair\n" if $debug; $datepair =~ /(\d{4})\/(\d{2})\/(\d{2}) (\d{2}):(\d{2}):(\d{2})\|(\d +{4})\/(\d{2})\/(\d{2}) (\d{2}):(\d{2}):(\d{2})/; my ($y1,$m1,$d1,$h1,$min1,$s1) = ($1,$2,$3,$4,$5,$6); my ($y2,$m2,$d2,$h2,$min2,$s2) = ($7,$8,$9,$10,$11,$12); my $yyyymmdd1 = $y1 . $m1 . $d1; my $yyyymmdd2 = $y2 . $m2 . $d2; my $same_day = 0; if ($yyyymmdd1 eq $yyyymmdd2) { $same_day = 1; } my ($day_start1,$day_end1) = &day_ctime_range($yyyymmdd1); my ($day_start2,$day_end2) = &day_ctime_range($yyyymmdd2); my $date1 = timelocal($s1,$min1,$h1,$d1,$m1-1,$y1); my $date2 = timelocal($s2,$min2,$h2,$d2,$m2-1,$y2); if ($date1 < $day_start1) { $date1 = $day_start1; } if ($date1 > $day_end1) { $date1 = $day_end1; } if ($date2 < $day_start2) { $date2 = $day_start2; } if ($date2 > $day_end2) { $date2 = $day_end2; } my $date1_secs; my $date2_secs; if ($same_day) { if ($date1 > $date2) { print "ERR: $date1 > $date2\n"; } $date1_secs = $date2 - $date1; $date2_secs = 0; } else { $date1_secs = $day_end1 - $date1; $date2_secs = $date2 - $day_start2; } if (&non_workday($date1)) { $date1_secs = 0; } if (&non_workday($date2)) { $date2_secs = 0; } print "date1_secs=$date1_secs\n" if $debug; print "date2_secs=$date2_secs\n" if $debug; my $days = 0; $date1 += 86400; while ($date1 < $day_start2) { my $nwday = &non_workday($date1); if ($nwday == 0) { $days++; } $date1 += 86400; } print "NUMBER_DAYS=$days\n" if $debug; my $seconds = ((8.5 * 60 * 60) * $days) + ($date2_secs + $date1_secs +); print ("((8.5 * 60 * 60) * $days) + $date2_secs + $date1_secs = $sec +onds\n") if $debug; return $seconds; } ######################## sub day_ctime_range { #for a given YYMMDD return start and end ctime my $today = shift(); $today =~ /^(\d\d\d\d)(\d\d)(\d\d)$/; my ($yyyy,$mm,$dd) = ($1,$2,$3); my $start = timelocal(0,30,8,$dd,$mm-1,$yyyy-1900); my $end = timelocal(0,0,17,$dd,$mm-1,$yyyy-1900); return ($start,$end); } ######################################## sub non_workday { my ($some_time) = shift; my $non_workday = 0; # check if a day is a holiday my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = loc +altime($some_time); my $yyyy = $year+1900; my $mm = $mon+1; my $yyyymmdd= sprintf ("%4d%02d%02d", $yyyy, $mm, $mday); #check list of holidays from table $non_workday = 1 if exists $holidays{$yyyymmdd}; if ($wday == 0 || $wday == 6) { $non_workday = 1; } return ($non_workday); } ########################################

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://554871]
Approved by ikegami
Front-paged by ikegami
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2021-09-22 00:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?