Web Connection
wwJSONSerializer returning wrong date times
Gravatar is a globally recognized avatar based on your email address. wwJSONSerializer returning wrong date times
  Scott R
  All
  Jun 2, 2020 @ 12:38pm

Hey Rick,

I'm running into an issue with the wwJSONSerializer. I have a datetime in a cursor and it's converting the date time incorrectly.

My cursor looks like:

For the serializing, I'm doing:

SELECT tWOs
LOCAL oWWJSON
oWWJSON = CREATEOBJECT('wwJSONSerializer')

oWWJSON.assumeUTCDates = .T.

m.retval = oWWJSON.serialize('cursor:')

The string it returns looks like: [{"jobsid":"0000000021","woid":"0000000022","starttm":"0700","dt":"2020-06-01T00:00:00","schedstart":"2020-06-01T06:59:59.999Z"},{"jobsid":"0000000021","woid":"0000000023","starttm":"1000","dt":"2020-06-02T00:00:00","schedstart":"2020-06-02T09:59:59.999Z"}]

It looks like it's rounding the times. For example, it's returning 2020-06-02T09:59:59.999Z instead of 2020-06-02T10:00:00Z.

Is there a simple setting I'm missing?

Thanks,

Scott

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Scott R
  Jun 2, 2020 @ 06:27pm

Yes I can verify this.

It's pretty freaking weird though. It looks like there's some problem with Cursors/Tables, but not with raw dates.

Doing this:

? TRANSFORM(GetUtcTime(DATETIME()))
? TRANSFORM(GetUtcTime({^2020-06-03 19:00:00}))
? TRANSFORM(GetUtcTime({^2020-06-02 20:00:00}))


? TRANSFORM(JsonDate(DATETIME()))
? TRANSFORM(JsonDate({^2020-06-03 19:00:00}))
? TRANSFORM(JsonDate({^2020-06-02 20:00:00}))

All works correctly. Both of these functions call .NET to provide the date conversions.

However doing the same with cursor dates:

CREATE CURSOR TQuery ( Name c(10), entered T)

INSERT INTO TQuery (Name,entered) VALUES ("rick",{^2020-06-02 20:00:00})
INSERT INTO TQuery (Name,entered) VALUES ("rick",{^2020-06-03 19:00:00})

SELECT TQuery
SCAN 
	? TRANSFORM(JsonDate(entered))   + "   orig: " + TRANSFORM( entered )
ENDSCAN

does not. Something happening in the cursor that's causing the time to get turned into a float value. In this case on the second value is a problem.

Not sure what to do about this though. I used to use raw FoxPro code but the problem with that is that you still need UTC conversion for that to work and that was not trivial and slow to do in FoxPro code as you basically had to calculate the timezone offset - which requires several API calls and calculation so that was never a good solution. The .NET code is considerably faster and is more reliable.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Scott R
  Rick Strahl
  Jun 3, 2020 @ 06:23am

Glad to know I'm not going crazy 😉

Just trying to think of a work around, this may not be a great idea but since I'm always setting assumeUTCDate = .T., I wonder if I can just change wwJsonSerializer.prg and change some code in the writeDate() function?

Change

this.cOutput = this.cOutput +   ;
    this.oBridge.InvokeStaticMethod("Westwind.WebConnection.wwJsonSerializer",;
    "SerializeJsonUtcDate",;
    lvValue,;
    THIS.AssumeUtcDates)

To

this.cOutput = this.cOutput + TTOC(lvValue, 3)

I'm not super thrilled about this solution but I think it should work if you can't find a way around this bug? I get that this only works because I'm setting assumeUTCDates = .T. so not a great solution if I ever have to change that...

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Scott R
  Jun 3, 2020 @ 10:07pm

The problem with that is that the date is not a UTC data which would be like lAssumeUtcDate false. The created dates are always UTC adjusted.

I think the trick might be to do use TTOC() but determine the DateTimeOffset reliably in .NET then cache the value so it doesn't have to be called for every conversion.

loNow = loBridge.GetStaticProperty("System.DateTimeOffset","Now")
lnMinutes = loBridge.GetPropertyEx(loNow,"Offset.TotalMinutes")
? lnMinutes

ltDate = DateTime()
lcJsonDate = ["] + TTOC(ltDate + (-1 * lnOffsetMinutes * 60)) + ["]

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Scott R
  Jun 3, 2020 @ 10:12pm

Ok... so I screwed around with this a bit and I think I have this working.

I changed wwJsonSerializer::WriteDate() to this:

FUNCTION WriteDate(lvValue)
LOCAL loNow

IF VARTYPE(lvValue) = "D"
	lvValue = DTOT(lvValue)
ENDIF	
IF EMPTY(lvValue)
	lvValue = {^1970-1-1 :}
ENDIF


IF THIS.nUtcTimeOffsetMinutes == -1
	IF ISNULL(this.oBridge)
		THIS.oBridge = GetwwDotnetBridge()
	ENDIF
	
	THIS.nUtcTimeOffsetMinutes = this.oBridge.oDotnetBridge.GetLocalDateTimeOffset()
	*this.nUtcTimeOffsetMinutes = THIS.oBridge.GetProperty(loNow,"Offset.TotalMinutes")	
ENDIF

IF (!this.AssumeUtcDates)
    *** Date is not UTC formatted so add UTC offset
	THIS.cOutput = this.cOutput + ["] + TTOC(lvValue + (-1 * this.nUtcTimeoffsetMinutes * 60),3) + [Z"]
ELSE
	THIS.cOutput = this.cOutput + ["] + TTOC(lvValue,3) + [Z"]
ENDIF

This needs a new helper function .GetLocalDateTimeOffset() in wwdotnetbridge.dll to work. The code I posted previously doesn't actually work in the current version due to a bug I just fixed related to ComValue conversions. So no matter what to make this work a new version of wwDotnetBridge.dll is required.

You can grab the updated dll from:

https://west-wind.com/files/WebConnectionExperimental.zip

Using that code seems to work to get proper UTC dates and not run into the funky rounding errors, plus as a bonus this code should run faster as a COM Interop call is avoided (except for one).

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Rick Strahl
  Jun 4, 2020 @ 02:06am

Gave this some more thought and although the solution in the last message sound good it has a big problem:

The UTC offset varies depending on the status of Daylight savings time. So the encoding needs to take that into account which means that solution won't work. I ended up adding a date specific version of the GetLocalDateTimeOffset() function in wwDotnetBridge.

However after thinking about this a bit more I tackled this a little differently: Rewriting the date as it's coming in and checking the milliseconds - if set and over 500 round up the second. Sounds easy enough except then the whole date has to be checked. I ended up with some nasty code that works:

/// <summary>
/// Turns a date string into a JSON date string
/// </summary>
/// <param name="time">Time to JSON encode</param>
/// <param name="isUtc">if false time is adjusted to UTC before serializing to remove timezone info</param>
/// <remarks>
/// There are problems with dates from a FoxPro table passed over COM
/// into this function due to floating point rounding. Works fine with
/// explicitly defined dates.
/// </remarks>
/// <returns></returns>
public string ToJsonUtcDate(DateTime time, bool isUtc)
{
    // fix rounding errors
    int second = time.Second;
    int minute = time.Minute;
    int hour = time.Hour;
    int millisecond = 0;
    if (time.Millisecond > 500)
    {
        second = time.Second + 1;
        if (second > 59)
        {
            minute = minute + 1;
            second = 0;
        }
        if (minute > 59)
        {
            hour = hour + 1;
            minute = 0;
        }
        if (hour > 23)
        {
            hour = 23;
            minute = 59;
            second = 59;
            millisecond = 999; 
        }
    }

    // we need to fix the date because COM mucks up the milliseconds at times
    var dt = new DateTime(time.Year, time.Month, time.Day, hour, minute, second, millisecond );

    if (!isUtc)
        dt = dt.ToUniversalTime();
    var json = JsonConvert.SerializeObject(dt,wwJsonSerializer.jsonDateSettings);
    return json;
}

The only failure point here is a time of 00:00:00 if it has rounded milliseconds because that would cause the date to overflow. So if that's the case it'll still go to 23:59:59 if it rounds down. In testing it looks like that date is fine though (no milliseconds showing up).

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Scott R
  Rick Strahl
  Jun 4, 2020 @ 06:30am

Thanks! This looks like it will be a good solution.

You mentioned on the last post you added a function to wwDotnetBridge. Is this the .prg or the .dll? If it's the .dll, is the same link you provided in the previous post the one I should use to get the .dll? I'm also assuming that the code you posted in wwJSONSerializer::WriteDate() is still the same?

Thanks for tracking this down and getting a fix so quickly!

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Scott R
  Jun 4, 2020 @ 11:18am

For this last fix to work you need an updated wwdotnetbridge.dll which is in the WebConnectionExperimental.zip file I mentioned previously. This has a new function that is now used as well as a fix to the old function which is what the existing code is using - they do the same thing but the new function is more efficient because it's a direct virtual method call instead of a static method access that requires .NET Reflection.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Scott R
  Rick Strahl
  Jun 4, 2020 @ 12:53pm

Hey Rick,

So I updated the wwJSONSerializer::WriteDate() function (From your post) and replaced the wwDotNetBridge.dll and .prg. I am now getting the following error:

{"isCallbackError":true,"message": "OLE error code 0x80070057: The parameter is incorrect.\rProc: writedate\rLine No: 393\rLine Contents: THIS.nUtcTimeOffsetMinutes = this.oBridge.oDotnetBridge.GetLocalDateTimeOffset()" }

Am I missing something or have you made a change since you posted the new code for writeDate()?

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Scott R
  Scott R
  Jun 4, 2020 @ 01:10pm

Never mind. I undid the changes to wwJSONSerializer::writeDate() and now it's working.

Thanks again for your help on this

Gravatar is a globally recognized avatar based on your email address. re: wwJSONSerializer returning wrong date times
  Rick Strahl
  Scott R
  Jun 4, 2020 @ 02:23pm

Sory if I wasn't being clear through all of these messages and multiple changes 😃

With the new DLL you should be able to either use the original code as it was, or you can change to this slightly better performing version that will be in the next release calling the newly added function:

************************************************************************
* wwJsonSerializer ::  WriteDate
****************************************
***  Function: Turns a date into an ISO formatted date value string
***    Assume: Input dates are assumed to be local dates
***            If you have UTC dates to start you'll need to convert
***            them to local dates first
***      Pass: Date Value
***    Return: Json Date 
************************************************************************
FUNCTION WriteDate(lvValue)
LOCAL loNow

IF VARTYPE(lvValue) = "D"
	lvValue = DTOT(lvValue)
ENDIF	
IF EMPTY(lvValue)
	lvValue = {^1970-1-1 :}
ENDIF

IF ISNULL(this.oBridge)
	THIS.oBridge = GetwwDotnetBridge()
ENDIF
this.cOutput = this.cOutput + THIS.oBridge.oDotnetBridge.ToJsonUtcDate(lvValue, this.AssumeUtcDates)

ENDFUNC
*  wwJsonSerializer ::  WriteDate
© 1996-2024