如何使用PowerShell将CSV文件内的UTC时间转换为EST时间

核心实现逻辑 #

你原有代码的修改点集中在Select-Object中Date & Time字段的表达式部分，无需改动其他逻辑：

1. 先读取系统内置的东部标准时区配置，自动适配夏令时规则，无需手动计算UTC偏移量
2. 将读取到的UTC时间字符串转为DateTime类型后转换为EST时间
3. 按你需要的12/24小时制格式输出即可

完整修改后代码 #

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"
    # 加载EST时区配置，Windows环境使用该ID，Linux/macOS替换为'America/New_York'
    $estZone = [TimeZoneInfo]::FindSystemTimeZoneById("Eastern Standard Time")
 
    $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' }
        # 24小时制EST时间 如果需要12小时制就用下方注释的表达式替换当前行
        @{Name = 'Date & Time'; Expression = {
            [DateTime]$utcTime = $_.CreationTime
            $estTime = [TimeZoneInfo]::ConvertTimeFromUtc($utcTime, $estZone)
            return $estTime.ToString('yyyy-MM-dd HH:mm:ss')
        }}
        # 12小时制EST时间 带AM/PM标识
        # @{Name = 'Date & Time'; Expression = {
        #    [DateTime]$utcTime = $_.CreationTime
        #    $estTime = [TimeZoneInfo]::ConvertTimeFromUtc($utcTime, $estZone)
        #    return $estTime.ToString('yyyy-MM-dd hh:mm:ss tt')
        # }}
        @{Name = 'Type of Action'; Expression = 'Operation' }
        @{Name = "Criteria"; Expression = "RecordType" }
        @{Name = "Item Search"; Expression = "ObjectId" }
        @{Name = "Result Status"; Expression = "ResultStatus" }
    ) | Export-Csv $modifiedFile -NoTypeInformation
}

效果示例 #

你提供的示例UTC时间2021-12-01T18:23:21转换后：

• 24小时制输出：2021-12-01 13:23:21
• 12小时制输出：2021-12-01 01:23:21 PM

内容的提问来源于stack exchange，提问作者ziico