CODEWITHSUNDEEP
powershelltimespan
sdcrytek
sdcrytek

Reputation: 25

Script to Sum up Total Call Duration in Powershell

I'm putting a function together in powershell that will read a SQL table's value for TotalCalls and TotalDuration. Since the call identifier is unique, I created a hash table that will have the call identifier as a key with TotalCalls and Total Duration as values. The report will eventually span months calculating a full report with the sum of TotalCalls and TotalDuration. I'm running into the issue with summing the TotalDuration since its a string with "hh:mm:ss". I know I have to convert it to a timespan or int32 data type in order to calculate it but my mind is running in circles on how to do it.

 if ($FullReport)
{
    #Querying ALL ARD Lines
    $FullQuery = Select-SQL -Database Telecom_Database -Query 'SELECT * FROM ARDReports'

    $TotalDuration = 0
    $TotalCalls = 0

    $FullHash = [ordered]@{}

    foreach ($row in $FullQuery)
    {
        if ($FullHash.contains($row.AOR))
        {
            $FullHash[$row.AOR].TotalCalls += [int]$row.TotalCalls
            #$FullHash[$row.AOR].TotalDuration += formattime $row.TotalDuration

        } else
          {
            $FullHash.Add($row.AOR, [ordered]@{})
            $FullHash[$row.AOR].add("Name",$row.Name)
            $FullHash[$row.AOR].add("Description",$row.Description)
            $FullHash[$row.AOR].add("TotalCalls",[int]$row.TotalCalls)
            $FullHash[$row.AOR].add("TotalDuration",$row.TotalDuration)

          }  

    }

The line #$FullHash[$row.AOR].TotalDuration += formattime is where it will keep adding up TotalDuration based on the month is whats making me go crazy. I was thinking of creating a separate function that would calculate it into a timespan but I don't know the correct way to approach. Any help be appreciated!

Thanks!

Upvotes: 1

Views: 565

Answers (3)

lit
lit

Reputation: 16236

The string can be converted into a TimeSpan and aggregated.

$durations = @('00:22:00', '1:04:22', '12:01:01')
$tssum = New-TimeSpan

foreach ($duration in $durations) {
    $tssum += [timespan]$duration
    $tssum
}

Upvotes: 0

boxdog
boxdog

Reputation: 8442

You can convert from "hh:mm:ss" format to a timespan object like this:

$timeSpan = [TimeSpan]::Parse("12:34:08")

This creates an object with these properties:

Days              : 0
Hours             : 12
Minutes           : 34
Seconds           : 8
Milliseconds      : 0
Ticks             : 452480000000
TotalDays         : 0.523703703703704
TotalHours        : 12.5688888888889
TotalMinutes      : 754.133333333333
TotalSeconds      : 45248
TotalMilliseconds : 45248000

You could add the TimeSpans directly or sum them as, say, ticks:

$FullHash[$row.AOR].TotalDuration += [TimeSpan]::Parse($row.TotalDuration).Ticks

You can convert it back later with a bit of string formatting:

[TimeSpan]::FromTicks($FullHash[$row.AOR].TotalDuration).ToString("dd\.hh\:mm\:ss")

Which gives output similar to this:

03.14:00:08

Upvotes: 1

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174505

I'm running into the issue with summing the TotalDuration since its a string with "hh:mm:ss". I know I have to convert it to a timespan [...]

This is pretty straightforward in .NET - you can use TimeSpan.Parse():

$timespan = [timespan]::Parse("04:52:13")

or in your situation:

$timespan = [timespan]::Parse($row.TotalDuration)

Upvotes: 0

Related Questions