Powershell: ConvertFrom-JSON incorrectly deserializes dates to DateTime

[sba923]

When ConvertFrom-JSON guesses something in the input data can be converted to a DateTime, it (silently) does so.

But the conversion doesn't correctly interprets some strings.

Steps to reproduce

I'm running this on a system that's on UTC+2 (CEST).

Write-Host("PowerShell version: " + $PSVersionTable.PSVersion.ToString())
$date = "2020-09-07T09:44:13.769Z"
Write-Host ("Original string: " + $date)
Write-Host("Cast to Datetime: " + [datetime] $date)
$json = ('[{"start":"' + $date + '"}]') 
Write-Host("JSON data: " + $json)
$data = $json |  ConvertFrom-Json
Write-Host($data[0].start.GetType().Name + " resulting from ConvertFrom-JSON: " + $data[0].start)

Expected behavior

PowerShell version: 7.0.3
Original string: 2020-09-07T09:44:13.769Z
Cast to Datetime: 09/07/2020 11:44:13
JSON data: [{"start":"2020-09-07T09:44:13.769Z"}]
DateTime resulting from ConvertFrom-JSON: 09/07/2020 11:44:13

Actual behavior

PowerShell version: 7.0.3
Original string: 2020-09-07T09:44:13.769Z
Cast to Datetime: 09/07/2020 11:44:13
JSON data: [{"start":"2020-09-07T09:44:13.769Z"}]
DateTime resulting from ConvertFrom-JSON: 09/07/2020 09:44:13

Environment data

Name                           Value
----                           -----
PSVersion                      7.0.3
PSEdition                      Core
GitCommitId                    7.0.3
OS                             Microsoft Windows 10.0.17763
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

The same issue is still present in 7.1.0-preview.6.

[sba923]

FWIW, here's my current workaround:

````
$JSONdata = (Invoke-WebRequest $url).Content

workaround for https://github.com/PowerShell/PowerShell/issues/13592

$marker = '#@#@#'
$JSONdata2 = $JSONdata -replace '("timestamp":")([^"]+)(")', ('$1' + $marker + '$2' + $marker + '$3')
$objects = ($JSONdata2 | ConvertFrom-Json).items | Foreach-Object { $_.timestamp= [datetime] ($_.timestamp-replace $marker, ''); $_ }
````

HTH

[kvprasoon]

Confused. Are you sure on the expected behavior ? Isn't the original date string and the json converted string same ? Isn't that the expected behavior ?

[sba923]

Nope, the expected behavior is that the JSON deserializer converts any string representing a date the exact same way [datetime]$string does it.

@SteveL-MSFT

BTW, I wonder how that deserializer guesses that a certain field in the JSON is a date and has to be converted to a DateTime... (I know, I could look at the code...)

[mklement0]

@sba923, ConvertTo-Json in v6+ creates a [datetime] instance with a .Kind value as follows:

• Unspecified, if there is no time-zone information in the input string.
• Utc, if the time-zone information is a trailing Z
• Local, if the time-zone information is given as a trailing UTC _offset_ (e.g., +02:00), in which case the offset is properly converted to the caller-local equivalent.

Therefore, in your example a Utc [datetime] instance is constructed.

The default output formatting / .ToString() output for such an instance does _not_ indicate the fact that it represents a UTC point in time, which is the source of the confusion.

To express such an instance as a _local_ (.Kind == Local) [datetime] instance, call its .ToLocalTime() method:

('"2020-09-07T09:44:13.769Z"' | ConvertFrom-Json).ToLocalTime()

As for what causes ConvertFrom-JSON in v6+ (via Json.NET) to interpret a _string_ value as a [datetime] instance: it is the _specific format_, that is an "o"-formatted (roundtrip format) string or a _prefix_ of it that at least includes everything up to the _seconds_ part (e.g., 2020-09-07T09:44:13).

[sba923]

Thanks for the clarifications.

Two comments:

1. this cannot be guessed, and definitely not from the documentation, so I submitted https://github.com/MicrosoftDocs/PowerShell-Docs/issues/6597
2. the .ToLocalTime() trick doesn't work for the _typical_ use case where ConvertFrom-JSON is used to convert something that represents an array of objects:

````
PS❯ ('[{"start":"2020-09-07T09:44:13.769Z"}]' | ConvertFrom-Json).ToLocalTime()

InvalidOperation: Method invocation failed because [System.Management.Automation.PSCustomObject] does not contain a method named 'ToLocalTime'.
````

[mklement0]

@sba923, thanks for opening the docs issue.

Note that the new System.Text.Json API that is being considered as the future underpinning of the *-Json cmdlets - see #11198 - exhibits the same behavior.

Yes, it is nontrivial to convert all deserialized-as-[datetime] properties to local dates, especially _in place_ - see the code in the bottom section.

It would be nice if ConvertFrom-Json had a parameter that requests that [datetime] instances of a given type should be returned.

---

# Parse sample JSON that by default is parsed with [datetime] instances with 
# .Kind values 'Utc', 'Local', and 'Unspecified', in order.
$fromJson = @'
[
  {"start":"2020-09-07T09:44:13.769Z"}, 
  {"start":"2020-09-07T09:44:13.769+02:00"},
  {"start":"2020-09-07T09:44:13.769"}
]
'@ | ConvertFrom-Json

# Convert any [datetime] instances in the object graph that aren't already 
# local dates (whose .Kind value isn't already 'Local') to local ones.
ForEach-Object -InputObject $fromJson {
  # Helper script block that walks the object graph.
  $sb = {
    foreach ($el in $args[0]) {
      # iterate over elements (if an array)
      foreach ($prop in $el.psobject.Properties) {
        # iterate over properties
        if ($dt = $prop.Value -as [datetime]) {
          switch ($dt.Kind) {
            'Utc' { $prop.Value = $dt.ToLocalTime() }
            # Note: calling .ToLocalTime() is not an option, because it interprets
            #       an 'Unspecified' [datetime] as UTC.
            'Unspecified' { $prop.Value = [datetime]::new($dt.Ticks, 'Local') }
          }
        }
      }
    }
  }
  # Call the script block with the input object.
  & $sb $_
}

# Output the array with the transformed [datetime] instances.
$fromJson

[mklement0]

@sba923, I've created a feature request for a -DateKind parameter for ConvertFrom-Json that would solve your problem: #13598

I think you can therefore close this issue now.

[iSazonov]

Nope, the expected behavior is that the JSON deserializer converts any string representing a date the exact same way [datetime]$string does it.

I am very amazed that you think so. NewtonSoft implementation is de-facto modern standard. New .Net implementation follow this. I believe users and developers expect that PowerShell follows the standard too and don't change the default behavior. As @mklement0 noted we could add new optional parameter to change the conversion but I'd prefer to see more feedback because I am sure it is again Formatting System task to show a data in a format user wants.

[sba923]

I know there are two sides to this coin:

• what data is returned by the conversion
• how that data is formatted when displayed or otherwise converted to string

PowerShell always has had a certain way to convert strings representing date and time to DateTime.

That's why with my PowerShell user hat on, I would expect that a string representing a date/time inside a JSON would be converted to DateTime the same way that same string would be converted outside a JSON container.

Even if I'm not too familiar with the .Kind property of DateTime objects.

Now there is another way of looking at things: apparently JSON users expect date/time conversions to behave a certain way that might not be the PowerShell way.

Which way should ConvertFrom-JSON be attracted?

@Steve_MSFT, do you have 'a policy' on this?

[iSazonov]

do you have 'a policy' on this?

The cmdlet correctly converts a datetime string to Datetime type value. If you want to get a local value you can use Datetime.ToLocalTime() method, if you want to get UTC - ToUniversalTime() method.

[sba923]

Indeed, once you know that there _is_ a .Kind property, you can convert to the appropriate flavor:

````
Write-Host("PowerShell version: " + $PSVersionTable.PSVersion.ToString())
$date = "2020-09-07T09:44:13.769Z"
Write-Host ("Original string: " + $date)
Write-Host("Cast to Datetime: " + [datetime] $date)
$json = ('[{"start":"' + $date + '"}]')
Write-Host("JSON data: " + $json)
$data = ($json | ConvertFrom-Json)
Write-Host($data[0].start.GetType().Name + " resulting from ConvertFrom-JSON: " + $data[0].start)
Write-Host($data[0].start.GetType().Name + " resulting from ConvertFrom-JSON -- '.Kind' property: " + $data[0].start.Kind)
Write-Host($data[0].start.GetType().Name + " resulting from ConvertFrom-JSON, with .ToLocalTime() applied: " + $data[0].start.ToLocalTime())

PowerShell version: 7.0.3
Original string: 2020-09-07T09:44:13.769Z
Cast to Datetime: 09/07/2020 11:44:13
JSON data: [{"start":"2020-09-07T09:44:13.769Z"}]
DateTime resulting from ConvertFrom-JSON: 09/07/2020 09:44:13
DateTime resulting from ConvertFrom-JSON -- '.Kind' property: Utc
DateTime resulting from ConvertFrom-JSON, with .ToLocalTime() applied: 09/07/2020 11:44:13
````

but if that's array data you get e.g. from some REST API, you'd have to iterate over _each and every_ DateTime object produced by ConvertFrom-JSON to apply .ToLocalTime to them.

Rather inconvenient if you ask me.

[mklement0]

Let's take a step back:

Use of [datetime]:

• [datetime] has several inherent limitations:

  • It only specifies an _absolute_ point in time if its .Kind is Utc.
  • With Local, it isn't self-contained: that is, only in relationship to the local time zone does it imply an absolute point in time.
  • With Unspecified, it is an _abstract_ point in time that is interpreted _differently_ when you call .ToLocalTime() (assumed to be UTC) vs .ToUniversalTime() (assumed to be local).
  • In terms of default formatting, a Utc instance _lacks any indication that the timestamp is indeed expressed in UTC_.

• [datetimeoffset] overcomes these limitations, but for the sake of backward compatibility we're stuck with [datetime] in many cases.

  • [datetimeoffset] doesn't even have a proper PowerShell display format at the moment (just does Format-List) and also lacks proper integration with respect to serialization - see #3172

In short: If backward compatibility weren't in the picture, [datetime] should be considered obsolete and replaced with [datetimeoffset].

---

[datetime] <string> vs. ConvertFrom-Json behavior

• I agree that these two behaviors differ is surprising, and that implementing something PowerShell-idiomatic justifies deviation from underlying API behavior.

• However, I find the behavior of [datetime] <string> ([datetime]::Parse(<string>, $null)) in itself counterintuitive behavior in that [datetime]::Parse('2020-01-01'), for instance, results in a instance with .Kind Unspecified rather than Local.

  • More sensibly, [datetimeoffset]::Parse('2020-01-01') assumes that the date is in the _local_ time zone.

---

Given the need for backward compatibility, I don't think we can change the current behavior, but what #13598 proposes should give you the desired functionality (asking that timestamps be normalized to UTC, local, or unspecified [datetime] or to [datetimeoffset] instances).

[sba923]

do you have 'a policy' on this?

The cmdlet correctly converts a datetime string to Datetime type value. If you want to get a local value you can use Datetime.ToLocalTime() method, if you want to get UTC - ToUniversalTime() method.

What's your definition of "correctly"?

The whole point seems to be that some people want the conversion to be "the same as PowerShell's [datetime] $string", some other people want the conversion to be "the usual way JSON is interpreted."

And those two methods are different.

Next to that, as I mentioned earlier, the behavior of ConvertFrom-JSON is very _inconvenient_ in the typical case where the JSON contains an array of objects. Hence the need for the workaround I shared above.