CODEWITHSUNDEEP
powershell
imrichardcole
imrichardcole

Reputation: 4675

ConvertTo-Json formatting

Running the following:

Get-Process | Select-Object Id,ProcessName,CPU,StartTime | ConvertTo-Json

And the resulting JSON shows the StartTime as:

"\/Date(1553511537485)\/"

How can I show the StartTime in a given date format please?

Upvotes: 2

Views: 1979

Answers (2)

phhu
phhu

Reputation: 1952

Try piping through something like this, which has the benefit of handling datetimes with unknown property names:

foreach-object {$_.Psobject.properties | foreach-object {if ($_.value -ne $null -and $_.value.gettype() -match '.*datetime'){$_.value=$_.value.toString("o")}};$_}

E.g. for the original question:

Get-Process | Select-Object Id,ProcessName,CPU,StartTime | foreach-object {$_.Psobject.properties | foreach-object {if ($_.value -ne $null -and $_.value.gettype() -match '.*datetime'){$_.value=$_.value.toString("o")}};$_} | convertto-json

Here StartTime is a datetime field, and so it converted to an ISO date in the output JSON.

    {
        "Id":  7192,
        "ProcessName":  "ZeroConfigService",
        "CPU":  19.703125,
        "StartTime":  "2020-04-20T16:59:44.1843370+01:00"
    }

...instead of:

    {
        "Id":  7192,
        "ProcessName":  "ZeroConfigService",
        "CPU":  19.71875,
        "StartTime":  "\/Date(1587398384184)\/"
    }

A similar approach can work for database results, where the field names might not be known:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery, getDate() anotherDate,'test' string" -ServerInstance "localhost" | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | convertto-json

...gives:

{
    "TimeOfQuery":  "\/Date(1587646812587)\/",
    "anotherDate":  "\/Date(1587646812587)\/",
    "string":  "test"
}

Whereas this:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery, getDate() anotherDate,'test' string" -ServerInstance "localhost" | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | foreach-object {$_.Psobject.properties | foreach-object {if ($_.value -ne $null -and $_.value.gettype() -match '.*datetime'){$_.value=$_.value.toString("o")}};$_} | convertto-json

...gives ISO formatted dates:

{
    "TimeOfQuery":  "2020-04-23T13:53:59.1230000",
    "anotherDate":  "2020-04-23T13:53:59.1230000",
    "string":  "test"
}

The date format can be changed by changing the parameter of the getString method toString("o"). "o" is for ISO date format. See (I think) under examples at https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring for details.

Note that the select-object in the pipeline is necessary, otherwise the foreach-object clause will attempt to update the properties of the original object, which may be read-only, and you probably don't want to do. (Does anybody know a better method for this?)

Upvotes: 3

Mark Wragg
Mark Wragg

Reputation: 23355

You could use a calculated property to perform the .ToString method on StartTime which would convert it to its default string format equivalent:

Get-Process | Select-Object Id,ProcessName,CPU,@{N='StartTime';E={$_.StartTime.ToString()}}| ConvertTo-Json

Example result:

 "StartTime":  "23/03/2019 08:55:06"

Upvotes: 2

Related Questions