How can I convert UTC to EST using PowerShell-CodePudding

In my csv file, I have Date and Time Columns but it's in UTC time so I'm just wondering how can I convert it to EST Time(either 12hr or 24hr format) using PowerShell. I have been searching how to do it on Google but I don't see any solution yet for Excel Column.


function Convert-JSONColumn {

    Write-Host "Start parsing Json Column"

    $downloadFile = Import-Csv "C:\AuditLogSearch\Downloaded-Audit-Log-Records.csv"
    $modifiedFile = "C:\AuditLogSearch\Modified-Audit-Log-Records.csv"
 
    $downloadFile | ForEach-Object {
        $jSON = $_.AuditData | ConvertFrom-Json
        $epdata = ""
        foreach ($ep in $jSON.extendedProperties) {
            $epdata  = $ep.Name   " : "   $ep.Value   "`n"
        }
        $paramdata = ""
        foreach ($param in $jSON.Parameters) {
            $paramdata  = $param.Name   " : "   $param.Value   "`n"
        }

        New-Object PsObject -Property @{
            RecordType            = $_.RecordType
            UserIds               = $_.UserIds
            Operations            = $_.Operations
            CreationTime          = $jSON.CreationTime
            Operation             = $jSON.Operation
            ObjectId              = $jSON.ObjectId
        }

    } | Select-object -Property @(
        @{Name = 'User'; Expression = 'UserId' }
        @{Name = 'Date & Time'; Expression = 'CreationTime' }
        @{Name = 'Type of Action'; Expression = 'Operation' }
        @{Name = "Criteria"; Expression = "RecordType" }
        @{Name = "Item Search"; Expression = "ObjectId" }
        @{Name = "Result Status"; Expression = "ResultStatus" }
    ) | Export-Csv $modifiedFile -NoTypeInformation

This is what it look like right now in UTC time.

2021-12-01T18:23:21
2021-12-01T18:23:21
2021-12-01T18:23:21

CodePudding user response：

Use TimeZoneInfo.ConvertTime():

# sample UTC datetime value
$dt = [datetime]::UtcNow

# define target time zone
$targettz = [System.TimeZoneInfo]::FindSystemTimeZoneById('Eastern Standard Time')

# convert to target timezone 
[System.TimeZoneInfo]::ConvertTime($dt, $targettz)

This will work for both localized and universal DateTime values.

If you want it to fail on encountering localized values, use ConvertTimeFromUtc():

# this will still work
$dt = [datetime]::UtcNow
[System.TimeZoneInfo]::ConvertTimeFromUtc($dt, $targettz)

# this will fail
$dt = [datetime]::Now
[System.TimeZoneInfo]::ConvertTimeFromUtc($dt, $targettz)

CodePudding user response：

Given a time that is in UTC in $json.CreationTime, you can parse it to a DateTime then convert with:

[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId([datetime]::Parse($json.CreationTime, $Null, [System.Globalization.DateTimeStyles]::AssumeUniversal),  'Eastern Standard Time').ToString('yyyy-MM-ddTHH:mm:ss')