Epoch time comparisons in microseconds

Question

I'm trying to wrap my head around Epoch time in PowerShell. Basically, I have a CSV that has a column with Epoch time in microseconds. I want to grab only records that fall within "yesterday midnight and today midnight UTC (but not including today midnight UTC").

Will something like this work? How do you convert a datetime to microseconds? I've only seen posts about seconds..

$yesterdayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddDays(-1)
$todayMidnight = Get-Date -Hour 0 -Minute 0 -Second 0
Import-Csv "Q:\data.csv" | ForEach-Object {
if (($_."Time-UTC-MicroSec" -le $yesterdayMidnight) -and ($_."Time-UTC-MicroSec" -lt $todayMidnight)) {
  # do stuff with records
}

Example file:

ID1    ID2 ID3 Time-UTC-MicroSec
2080101 26  501032  1483232054547470
2080101 5   501032  1483231158598830
2080101 30  501012  1483230264931800
2080101 28  501032  1483230721849650
2080101 28  501032  1483231194379190

Answer 1

I got the convert function from here. First you just have to divide by a million to convert microseconds back to seconds.

Function get-epochDate ($epochDate) { [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($epochDate))}

$yesterdayMidnight = (get-date -Hour 0 -Minute 0 -Second 0).AddDays(-1)
$todayMidnight = get-date -Hour 0 -Minute 0 -Second 0

Import-CSV "Q:\data.csv" | ForEach-Object {
    $dt = get-epochDate ([Double]($_."Time-UTC-MicroSec" / 1000000))
    if(($dt -ge $yesterdayMidnight) -and ($dt -lt $todayMidnight)) {
        #Do Stuff With Records
    }
}

Answer 2

To get a proper csv file I did a conversion with:

$FileIn = "Q:\Test\2017-01\06\SO_41514002.csv"
(gc $FileIn) -replace " +",","|Set-Content .\Test.csv

My approach was the other way round to convert dates to seconds

$FileIn = ".\Test.csv"
$unixEpochStart = new-object DateTime 1970,1,1,0,0,0,([DateTimeKind]::Utc)
$UTCNow = [DateTime]::UtcNow
$TodMid = new-object DateTime $UTCNow.Year,$UTCNow.Month,$UTCNow.Day,0,0,0,([DateTimeKind]::Utc)
$YesMid = $TodMid.AddDays(-1)
$TodMidSec = [int]($TodMid - $unixEpochStart).TotalSeconds
$YesMidSec = [int]($YesMid - $unixEpochStart).TotalSeconds

Import-CSV $FileIN | ForEach-Object {
    $dt = [int]($_."Time-UTC-MicroSec" / 1000000)
    "$YesMidSec $dt $TodMidSec"
    if(($dt -ge $yesMidsec) -and ($dt -lt $todMidsec)) {
    "in range"
    }
}

The output of the script shows the secs were just out of the range:

> .\SO_41514002.ps1
1483574400 1483232055 1483660800
1483574400 1483231159 1483660800
1483574400 1483230265 1483660800
1483574400 1483230722 1483660800
1483574400 1483231194 1483660800