Converting output from UTC to local time

Question

_{Sunday, July 19, 2015 4:53 AM}

Hi,

Periodically, I run a script like this that brings up a couple Out-Gridview windows with Exchange Online data. Exchange is all UTC all the time, but for the purposes of this display (there's one column that has the date and time), I'd like to convert it to local time.

The closest that I've come to finding something on this is a function that can do the conversion, but I don't really know how to incorporate it into something like this.

$dateEnd = get-date 
$dateStart = $dateEnd.AddHours(-48)

Get-MessageTrace -StartDate $dateStart -EndDate $dateEnd | Select-Object Received, Status, SenderAddress, RecipientAddress, Subject, Size, FromIP, MessageId, MessageTraceId, Index | Where {$_.Status -eq "Failed" -Or $_.Status -eq "FilteredAsSpam"} | Sort-Object Status | Out-GridView -Title "Message Tracking Data (since $dateStart)"

Get-MessageTrace -StartDate $dateStart -EndDate $dateEnd | Where {$_.Status -eq "Failed"} | Get-MessageTraceDetail | Select-Object Date, Event, Action, Detail, Data, MessageID | Sort-Object Date | Out-GridView -Title "Failed Messages (since $dateStart)"

All replies (6)

_{Sunday, July 19, 2015 9:10 AM ✅Answered}

If DateTimeKind of the datetime object is Utc you should be able to do this:

Get-MessageTrace -StartDate $dateStart -EndDate $dateEnd | 
    Where {$_.Status -eq "Failed"} | 
    Get-MessageTraceDetail | 
    Select-Object @{N='Date';E={$_.Date.ToLocalTime()}}, Event, Action, Detail, Data, MessageID | 
    Sort-Object Date | 
    Out-GridView -Title "Failed Messages (since $dateStart)"

If DateTimeKind is Local or Unspecified and you know it should be Utc, you can do this:

@{N='Date';E={[datetime]::SpecifyKind($_.Date,'Utc').ToLocalTime()}}

_{Sunday, July 19, 2015 5:15 AM}

The times in Exchange are system time objects.  They are normally displayed as local time.  Perhaps you don't understand how UTC works.

Are you trying to display the time local to the sender, the receiver or to you.  Those are the three possibilities.

What you are asking os vague and ambiguous.  Please try to think about and understand how times work.

What are you trying to display? YOu show a query but don't define what it is you need as an output.

Here is a hint.  Time is absolute.  How we display time is location relative.  Yoou need a location and a time relation to decide on what time you want.

UTC is stated as GMT plus TZ.  TZ is an offset +- to GMT.  EST is GMT -5.

Given that describe what you are asking for.  Don't worry about mistakes. Most admins do not get this the first timeout.

\(ツ)_/

_{Sunday, July 19, 2015 6:23 AM}

From what I've seen going back to Exchange 2003 logs up to the data available to admins for Exchange Online, the times that Exchange uses consistently are UTC (the times that end-users see are local, or course).

The lines above, in part, produce output such as "7/17/2015 6:26:29 PM" for one of the columns that is in fact not my local time but rather UTC. I know this by verifying the local time when a particular message actually was sent or received--plus, when using the Exchange Admin Center, MS even heads the column with "UTC". Exchange loves UTC.

All I'm looking to do for the purposes of the above output is the ability to convert that one column in each report to, say, GMT -5 to make it a little easier to read.

There isn't a command available to do that, but if there was I would pipe to it right after the "Sort-Object" part, such as:

Sort-Object Status | From-UTC Received | ......

and

Sort-Object Date | From-UTC Date | ...... 

_{Sunday, July 19, 2015 7:20 AM | 1 vote}

Function Get-LocalTime($UTCTime){
    $strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
    $TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
    $LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($UTCTime, $TZ)
    Return $LocalTime
}
Function Get-UTCTime($LocalTime){
    Return $LocalTime.ToUniversalTime()
}

$LocalTime = Get-Date
Write-Host "LocalTime = $LocalTime"
Write-Host "UTCTime = $(Get-UTCTime $LocalTime)"
$UTCTime = Get-UTCTime $LocalTime
Write-Host "LocalTime = $(Get-LocalTime $UTCTime)"

LocalTime = 07/19/2015 00:18:41
UTCTime = 07/19/2015 07:18:41
LocalTime = 07/19/2015 00:18:41

_{Sunday, July 19, 2015 10:00 AM}

It is very easy to do.  Just make string a time object and use the built in converter.  Add GMT to end of time strin.

PS >$time=[datetime]'7/19/2015 9:56:53 AM GMT'
**PS >$time ** # this will now be local time
Sunday, July 19, 2015 5:56:53 AM

Notice it is now GMT - 5 +DST

The datetime class is designed to do this.   No need for those old VBScript methods.

\(ツ)_/

_{Sunday, July 19, 2015 5:58 PM}

Thanks all!  Leif-Arne, that works perfectly.