Calculating average in PowerShell from EPOCH timestamp

Question

I have a problem with calculating (PowerShell) the average of the following data from a txt file.

First row  =  EPOCH Time 
Second row =  Response time (milliseconds)
Third row  =  Name

1451603439,297,NA
1451603440,203,NA
1451604606,328,OP
1451604645,203,NA
1451604646,234,NC
1451604647,234,NA
1451604647,202,NA
1451604649,234,NA
1451604650,187,NA
1451604651,195,OP
1451604652,245,NA
1451604653,203,NA
1451604653,218,NA
1451604654,234,OP
1451604655,203,NA
1451604656,187,NA
1451604657,156,NA
1451604658,171,NA
1451604658,187,NA
1451604659,156,NA
1451604660,218,NA

I want to calculate the average response time per day per name.

The problem I face is that I first must calculate the epoch to normal date. Then take all the values from one day and take the average response time per name and then save this to another file and repeat this step until the end of the file.

By row, you actually mean column? As how to convert the epoch time depends on whether it's seconds, milliseconds or something else. The first value maps to January 17th 1970 (ms) or 31 Dec 2015 (sec). — vonPryz, Jan 23 '17 at 09:35
Welcome to StackOverflow! What is the *actual* problem here and what have you tried? As this "question" stands, it looks like you're just waiting for someone to write a script for you. StackOverflow is *not* a free script writing service — Mathias R. Jessen, Jan 23 '17 at 09:36
You could start [here](http://stackoverflow.com/questions/10781697/convert-unix-time-with-powershell). — sodawillow, Jan 23 '17 at 09:42

score 1 · Answer 1 · answered Jan 23 '17 at 12:50

1

try something like this:

$origin = New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0

import-csv "C:\temp\file.TXT" -Header ColDate, ColNum, ColCode | 
    select ColNum, ColCode, @{N="ColDate"; E={$origin.AddSeconds($_.ColDate).Date }} | 
        group ColDate, ColCode  | 
            select Name, @{N="ColAverage";E={($_.Group.ColNum | Measure-Object -Average).Average}}

answered Jan 23 '17 at 12:50

Esperento57

16,521
3
39
45

Just out of interest, EPOCH time references are afaik mostly Utc based. So the averages aren't based on a local day but a 24h period shifted by Utc-offset. +1 for the solution. – Jan 23 '17 at 13:59
Perfect, works like a charm. I was trying to first convert->replace every EPOCH time and then i was grouping it. but this makes much more sense – Ruud Palmen Jan 23 '17 at 15:31

score 0 · Answer 2 · answered Jan 23 '17 at 14:46

Please find a possible solution. Also there are some comments in the code so we know what each part of the code is doing

#We get records from the file response.txt containing the format stuff,reponse-time,name
$file = Get-Content response.txt
#We create an array
$arr = [System.Collections.ArrayList]@()
#We will put each value separated by , into an object and will add each object in the array
Foreach ($result in $file) {
$tmp = $result.Split(",")
$obj = $null
$obj = New-Object System.Object
$obj | Add-Member -type NoteProperty -Name stuff -Value $tmp[0]
$obj | Add-Member -type NoteProperty -Name response -Value $tmp[1]
$obj | Add-Member -type NoteProperty -Name name -Value $tmp[2]
$arr += $obj
}
#We sort the array by name
$arr = $arr | Sort-Object name
$start=0
$i = 0
#we calculate average for each name until we reach the end of the array
while ($arr[$start]){
    $counter=0
    $tmpName=$arr[$start].name
    $time=0
    for($i=$start; $arr[$i].name -eq $tmpName; $i++,$counter++){
        $time+=$arr[$i].response
    }
    Write-Host "Average time for all "$counter" records with name "$tmpName" is "($time/$counter)
    $start=$i
}

Calculating average in PowerShell from EPOCH timestamp

2 Answers2