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

Hello All, I am currently using Active Perl 5.8.8 and have built a PERL script to parse data from an MS Access Database and output the results to MS Excel file. The problem I am having is I want to create time intervals (buckets of a given duration) and count how many messages there are per bucket but have a time format (5/19/2008 6:12:44 PM). 
Example:

Bucket Duration : 600 sec (10 min)

Input:
S_TIME                    CountOfMsg
5/19/2008 6:12:44 PM    4
5/19/2008 6:17:44 PM    4
5/19/2008 6:22:44 PM    4
5/19/2008 6:27:44 PM    4
5/19/2008 6:32:44 PM    4

Output: 
Interval Bucket            CountOfMsg
5/19/2008 6:12:00 PM    8
5/19/2008 6:22:00 PM    8
5/19/2008 6:32:00 PM    4

I am having problems when I enter the time (in the current format) int
+o a perl hash to compare if its < or > the interval.

[download]


So I need help converting the time to be rounded down into buckets. Can anyone give me suggestions on the easiest way to do this? Thanks in advance! Roy

Replies are listed 'Best First'.
Re: Converting a Date/Time
by pc88mxer (Vicar) on Jun 10, 2008 at 18:08 UTC
    The basic idea is that you need to convert a time string into a number of seconds. For your specific example, the following will work: 
    sub to_seconds {
  my $time_string = shift;
  my $secs;
  if ($time_string =~ m/(\d+):(\d+):(\d+)\s*(AM|PM)/i) {
    $secs = $1*3600 + $2*60 + $3 + (lc($4) eq 'pm' ? 12*3600 : 0);
  } else {
    warn "unable to parse this time: $time_string\n";
  }
  return $secs; # returns undef if unable to parse string
}

    [download]
    From here you should be able to figure out how to determine if two readings are in the same bucket.

    If you need to take into account the calendar day, have a look at the str2time routine in Date::Parse.

[reply]
[d/l]
[select]
      Thanks for the help. That is exactly what I needed. I was wondering how would I convert the seconds back to the same date/time format?
[reply]
        Date::Parse provides str2time to convert a time string into a "Unix time" (time in seconds) and strftime from the POSIX module is often used to convert a "Unix time" back into a string.
[reply]
[d/l]
[select]
Re: Converting a Date/Time
by Corion (Patriarch) on Jun 10, 2008 at 18:20 UTC

    Depending on whether the start of your buckets needs to be dynamic or fixed, you can move the whole logic into the database:

    select 
    -- this is database specific and I don't know the name of the Acce
+ss function
    floor(convert_to_seconds(S_TIME)/600) * 600 as bucket,
    count(*)
group by bucket

    [download]

    I don't have the Access manual, so I don't know what function you actually need to convert a date/time field to a number of seconds, but I expect a quick search in the help to turn up that link.

[reply]
[d/l]

Back to Seekers of Perl Wisdom