topic RE: JSON to Collection Weid Dates Issue in Product Forum

JSON to Collection Weid Dates Issue

AdamOllerenshaw — Fri, 17 Jan 2020 11:29:00 GMT

Hi

So we have come across a strange issue with JSON to Collection action not formatting some date/times correctly - date format is I believe called epoch (example of our date string being... \/Date(1555372800000)\/)

Once we convert our string into a collection using the above, some dates are being formatted as 1 day in the past with a time of 23:00. The dates being correctly formatted have time of 00:00. Weirdly it seems to be based on Month - November to March show correct date with time 00:00, April to October are wrong showing date-1 and time 23:00.

Using the above example through an online checker (epochconverter.com) shows GMT date as 16 April 2019 00:00:00. This string runs through the JSON to collection and date that comes out is 15 April 2019 23:00:00

Note this is Version 6.4

Has anyone come across this? or have any ideas on where to start looking to address?

hope to hear back

Regards

------------------------------
Adam Ollerenshaw
Process Automation Modeller
Computershare
Europe/Bristol - UK
------------------------------

RE: JSON to Collection Weid Dates Issue

zdenek.kabatek — Mon, 20 Jan 2020 08:45:00 GMT

Hi,

can't this be related to some daylight saving time and your regional settings?

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
Europe/Prague
------------------------------

RE: JSON to Collection Weid Dates Issue

NicholasZejdlik — Mon, 20 Jan 2020 15:01:00 GMT

The issue isn't with the JSON parser, rather it is because the results are being outputted as a datetime value. For testing purposes, I modified the JSON To Collection stage to output as text instead of date, which returns the expected GMT date. However, when that value is outputted as a DateTime, it gets converted to your local timezone.

Since you're in the UK, your timezone follows daylight savings whereas GMT does not, that is when you're seeing the hour difference. For me personally, it's a difference of several hours since I'm on CST.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: JSON to Collection Weid Dates Issue

AdamOllerenshaw — Mon, 20 Jan 2020 15:37:00 GMT

Hi Nicholas

Thanks for your reply, we deduced that it was a DST issue but were (and still are) trying to work around it by trying to format our source data as text, which is throwing up some other issues, so your response has got us excited!

We are pulling data back from SharePoint, but the thing is we aren't pre-defining fields so I'm wondering how you are outputting these fields to text. Did you make amendments to the JSON to Collection code stage? I'm not very knowledgeable on code stage updates, do you have any advice/tips on how we format the output collection as text?

thanks

------------------------------
Adam Ollerenshaw
Process Automation Modeller
Computershare
Europe/London
------------------------------

RE: JSON to Collection Weid Dates Issue

NicholasZejdlik — Mon, 20 Jan 2020 18:39:00 GMT

Add this code to the Global Code (Initialise page -> double click the page information box -> Global Code)

Private Function ConvertDateTimeToText(ByVal Table As DataTable) As DataTable
	Dim Result As New DataTable
	For Each Column As DataColumn In Table.Columns
		If Column.DataType = GetType(DateTime) Then
			Result.Columns.Add(Column.ColumnName, GetType(String))
		Else
			Result.Columns.Add(Column.ColumnName, Column.DataType)
		End If
	Next

	For Each Row As DataRow In Table.Rows
		Dim NewRow As DataRow = Result.NewRow()
		For Each Column As DataColumn In Table.Columns
			If Column.DataType = GetType(DataTable) Then
				NewRow(Column.ColumnName) = ConvertDateTimeToText(Row(Column.ColumnName))
			Else
				NewRow(Column.ColumnName) = Row(Column.ColumnName)
			End If
		Next
		Result.Rows.Add(NewRow)
	Next
	Return Result
End Function

Then modify the JSON To Collection stage with the following:

result = ConvertDateTimeToText(ConvertToDataTable(json))

All of the fields returned in the JSON conversion will be changed from DateTime to Text, which will keep Blue Prism from trying to automatically convert the DateTime. The ConvertDateTimeToText() function is recursive, so it will work with nested JSON as well.

On a trivial side note, I realized Blue Prism isn't trying to convert the DateTimes to local time, rather it is trying to convert from local time to UTC, which is why you ended up -1 hour instead of +1 hour. There may be a better way to handle this particular issue on version 6, but I am constrained to version 5 currently and this is the best solution I've found so far.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: JSON to Collection Weid Dates Issue

AdamOllerenshaw — Tue, 21 Jan 2020 08:37:00 GMT

thanks Nicholas for all your help i'll try this

------------------------------
Adam Ollerenshaw
Process Automation Modeller
Bristol/United Kingdom
------------------------------

RE: JSON to Collection Weid Dates Issue

jgregor4 — Mon, 19 Jul 2021 08:29:00 GMT

Hi Nicholas,
I have been having the same issue in that Date, Time, DateTime all come out as DateTime which causes issues with the validation of data.
I attempted to use the code you supplied however after copying the code into the Global Code and modifying the stage i get an error of:

Internal : Could not execute code stage because exception thrown by code stage: Unable to cast object of type 'System.String' to type 'System.Data.DataTable'.

I am very unfamiliar with code stages so unsure what the cause/solution would be - are you able to help?

------------------------------
James Gregory
RPA Developer
Europe/London
------------------------------

RE: JSON to Collection Weid Dates Issue

fostercarly — Mon, 02 Aug 2021 05:48:00 GMT

JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with dates in JSON and really JavaScript in general – is that there's no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:

var jsonDate = new Date(1297246301973);

Then let's convert it to js format:

var date = new Date(parseInt(jsonDate.substr(6)));

The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .

------------------------------
foster carly
------------------------------

RE: JSON to Collection Weid Dates Issue

NathanAlex — Mon, 02 Aug 2021 19:56:00 GMT

The issue isn't with the JSON parser,

rather it is because the results are being outputted as a datetime value.

For testing purposes,

I modified the JSON To Collection stage to output as text instead of date,

which returns the expected GMT date.

However, when that value is outputted as a DateTime,

it gets converted to your local timezone.

Since you're in the UK, your timezone follows daylight savings whereas GMT does not,

that is when you're seeing the hour difference.

For me personally, it's a difference of several hours since I'm on CST.
------------------------------
The Heller Approach
------------------------------

RE: JSON to Collection Weid Dates Issue

AndreyKudinov — Wed, 04 Aug 2021 14:19:00 GMT

Usually you just need to apply .ToLocalTime() before returning datetime to Blueprism objects from code stage to avoid this mess. This small change in global code of json VBO should do the trick. Make sure it doesn't break other processes, maybe make a copy of that VBO.

    Private Function DeserialiseGeneric(ByVal o As Object, ByVal populate As Boolean) As Object
        Dim a As JArray = TryCast(o, JArray)
        If a IsNot Nothing Then
            Return DeserialiseArray(a, populate)
        End If

        Dim jo As JObject = TryCast(o, JObject)
        If jo IsNot Nothing Then
            Return DeserialiseObject(jo, populate)
        End If

        Dim jv As JValue = TryCast(o, JValue)
        If jv IsNot Nothing Then
			If GetTypeOf(jv.Value) = GetTypeOf(datetime) Then
				Return jv.Value.ToLocalTime()
			Else
				Return jv.Value
			End if
        End If

        Return JSON.Null
    End Function

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: JSON to Collection Weid Dates Issue

BhawanaDaultani — Fri, 14 Jan 2022 17:48:00 GMT

RE: JSON to Collection Weid Dates Issue

warrenfelsh — Mon, 16 May 2022 07:22:00 GMT

The JSON specification does not specify a format for exchanging dates which is why there are so many different ways to do it. JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with JSON date and really JavaScript in general – is that there's no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:

var jsonDate = new Date(1297246301973);

Then let's convert it to js format:

var date = new Date(parseInt(jsonDate.substr(6)));

The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .

For ISO-8601 formatted JSON dates, just pass the string into the Date constructor:

var date = new Date(jsonDate);

------------------------------
warren felsh
------------------------------