OT:Before DST change: Json date questions

[spl]

Signaled as OT:

When datetime stamps are returned in json format, they might either look like the ISO timestamp or a json object like ( "\/Date(1211702472000)\/" ).

Attached a .txt file which [if converted .txt=>.json] so rename it is a valid json output with 2 dates in that format. Does the current json extender/or Jim's extender calculate them correctly as datetime types? More to come.

[td]

The date data type and function are Javascript. JSON is a subset of Javascript with the data types, NULL, number, string, boolean, array, and object but nothing else. The date function in your text file is treated as the type "string" in JSON. The WIL JSON extender is designed to be a JSON parser but it is currently not a Javascript parser.

[spl]

The date data type and function are Javascript. JSON is a subset of Javascript with the data types, NULL, number, string, boolean, array, and object but nothing else. The date function in your text file is treated as the type "string" in JSON. The WIL JSON extender is designed to be a JSON parser but it is currently not a Javascript parser.

Understood. But even as parsing as string, you should come up with a time in the data as 08:01 AM...  So, here is what I am running across: the data is part of a query from the MS ContosoSales which I collected in both Access and SQL Server as datetimes then exported to json. I used PS functions but wanted to simulate how json data can arrive from a REST query. I found that the PS ConverTo-Json would give the time as 12:01 PM, discovering that the function treats data in UTC time. So, I changed the timezone to (1) first GMT time, which recognizes daylight saving, but that changed 08:01 to 07:01 (2) Greenwhich Time, which does not recognize DST and it converted correctly. I googling... one respondent to a thread indicated that the PS function treats dates as utc.. 

Converting the json to .csv or another format is not an issue, but converting it appropriate to the source seemed to have to take into consideration [in my case] starting the script with Greenwich utc  timezone, creating the json, re-setting timezone to EST.

Then my final aaarrrghhh! It worked fine from SQL Server in Win10, but needed the timezone borders to work in Win11....

Basically will be running this Sunday after the DST change and see how PS functions react!! If WB parser has no such timezone issues but can still create json from a database for export and conversion then this conversation = $Null

[td]

Understood. But even as parsing as string, you should come up with a time in the data as 08:01 AM...

Not sure what you are referring to by "you" in the sentence above. But as previously mentioned full javascript parsing including the javascript "date" function is not part of the JSON standard so there is no "should" nor expectation involved.

[spl]

Understood. But even as parsing as string, you should come up with a time in the data as 08:01 AM...

Not sure what you are referring to by "you" in the sentence above. But as previously mentioned full javascript parsing including the javascript "data" function is not part of the JSON standard so there is no "should" nor expectation involved.

By you I meant y'all or the user. I understand issue and lack of date type in Json, and I tried to explain that was not the point - the point being how would WB translate the json I submitted. Period.

[td]

Since your JSON data is valid the JSON Extender would parce it as an array of objects. Each object would contain a couple of string name/value pairs. Each value returned by the jsGetValue function would be a string like "/Date(1211702472000)/". WinBatch does not support javascript directly so you would either need to parse and convert it using logic in a script, find a COM Automation or .Net library that processes Javascript, or find a utility program that you can run to process the function. The Nodejs runtime comes to mind but that seems like a lot of overkill for such a humble task. Those a just suggestions of the top so there may be other better approaches. 

[spl]

Appreciate the comments. I threw together a quick snippet as I believe the json date format is the number of milliseconds since 1/1/1970 (an I an positive there are better methods)

Code (WINBATCH) Select Expand


AddExtender("WWHUG44I.DLL", 0, "WWHUG64I.DLL")
d="/Date(1211702472000)/"
d1=strlen(d)
timestamp=""
for i = 1 to d1
   d2 = strsub(d,i,1)
if IsNumber(d2) then timestamp = timestamp:d2
next
Message(d,timestamp)
num2 = "1000"
x= huge_Divide(timestamp,num2)
Message("",x)
unixBase="1970:01:01:00:00:00"
Message(unixBase,TimeAdd(unixBase,"0000:00:00:00:00:%x%") )


[td]

If you have a recent version of WinBatch it becomes a tiny bit simpler

Code (winbatch) Select Expand

jsDate = "/Date(1211702472000)/"
DateTicks = StrTrimChar(jsDate, "/Date() ")

Offset = DateTicks/1000
UTC = TimeAdd("1970:01:01:00:00:00","0000:00:00:00:00:":Offset)

;; Assuming time is UTC...
EST = TimeZoneConvert(UTC, "UTC", "Eastern Standard Time")
Formated = TimeFormat(EST, "MMM d, yyyyy HH:mm:ss t")
Message("Eastern Standard Time", Formated)


[spl]

I have TimeFormat(), but not TimeZoneConvert()... but correctly reformatting the json date eliminates the 2nd part of my initial issue. The 1st issue is how the code which transfers sql server datetime columns but w/out specifying time zone borders will create a json date that when parsed is either 4 or 5 hours off from the actual column data. So. on Sunday I can test if the offsets still occur and how best to handle them.

And, again, thanks for sticking with the thread... and if there is existing code to transfer a 64-bit SQL Server table query into json [used for an API request] that will be a moot point.

[td]

TimeZoneConvert will automagically adjust for daylight saving time based on the rules in effect for the year and timezone being converted from and to. Not any help for you but a spiffy feature that I like to mention whenever I get the chance...

[spl]

TimeZoneConvert will automagically adjust for daylight saving time based on the rules in effect for the year and timezone being converted from and to. Not any help for you but a spiffy feature that I like to mention whenever I get the chance...

I agree, quite spiffy, and well received by WB users. I had previously kludged a conversion with CLR code using System.TimeZoneInfo as at that time had need to convert from PST to EST.