determining work hours between 2 dates

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

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	[reply]
Re^2: determining work hours between 2 dates by tokpela (Chaplain) on Jun 12, 2006 at 19:53 UTC
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"; [download]	[reply] [d/l]
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.	[reply]
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.	[reply]
Re: determining work hours between 2 dates by sh1tn (Priest) on Jun 12, 2006 at 18:51 UTC
Maybe Date::Simple or Date::Calc.	[reply]
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..	[reply]
Re^2: determining work hours between 2 dates by CountZero (Bishop) on Jun 12, 2006 at 21:21 UTC
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	[reply]
Re^3: determining work hours between 2 dates by ww (Archbishop) on Jun 12, 2006 at 23:25 UTC
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).	[reply]
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.	[reply]
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	[reply]
Re^2: determining work hours between 2 dates by sgifford (Prior) on Jun 12, 2006 at 22:29 UTC
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. -- sgifford's Web page	[reply]
Re^2: determining work hours between 2 dates by tokpela (Chaplain) on Jun 13, 2006 at 02:45 UTC
>> 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.	[reply]
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.	[reply]
Re: determining work hours between 2 dates by kosie99 (Acolyte) on Jun 13, 2006 at 16:23 UTC
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	[reply]
Re: determining work hours between 2 dates by kosie99 (Acolyte) on Jun 23, 2006 at 10:55 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 ;) Read more... (4 kB)	[reply] [d/l]
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"; [download] Cheers, Matt	[reply] [d/l]
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); } ######################################## [download]	[reply] [d/l]
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); } ######################################## [download]	[reply] [d/l]


go ahead... be a heretic
	PerlMonks