Saved AdvancedCriteria for Date field with DATE_CONSTRUCTOR encoding and TimeZones

[stonebranch1]

I have a problem with a persisted AdvancedCriteria w/r Date fields and different browser timezones when using DATE_CONSTRUCTOR encoding of the criteria.

I was using the XML_SCHEMA form of encoding the AdvancedCriteria and since I have specific format/parsing (which handles round-tripping the dates) there was no problem with different browser TimeZones since dates were stored as "yyyy-MM-dd" (except BETWEEN operator see this forum post http://forums.smartclient.com/showthread.php?t=30392)

So now Date field criteria is stored as like this:

{
    "fieldName":"tdate", 
    "operator":"equals", 
    "value":new Date(1401595200000)
}

This Date equates to 2014-06-01 and since I created the AdvancedCriteria in Eastern TimeZone the exact date it equates to is 2014-06-01 00:00:00 -0400 in TimeZone US/Eastern

The problem comes when the browser is in Pacific Time and loads that filter up in the UI. Since the encoding has the Date object, and since that Date value in Pacific time is 2014-05-31 21:00:00 -0700 in TimeZone US/Pacific it now shows 2014-05-31 even though this is a Date field and not a DateTime field.

If I use that same Date field in a record set to June 1st, then the framework handles the transition of the Date object being sent from the server to the client so that no matter what TimeZone the browser is in, it always the same date.

For example the same record that has the server-side database date value saved as 2014-06-01 when retrieved in the Eastern TZ browser receives the value in the DS response as tdate:new Date(1401638400000) and when that same record is retrieved in the Pacific TZ browser, it gets tdate:new Date(1401649200000) which each of those date values equate to 1401638400000 = 2014-06-01 12:00:00 -0400 in TimeZone US/Eastern and
1401649200000 = 2014-06-01 12:00:00 -0700 in TimeZone US/Pacific respectively so all that works very nice.

Is there anything I need to do to make the AdvancedCriteria persistence behave the same way?

The field is defined as type Date (not DateTime

<field name="tdate" type="date" required="true"/>

Attached is screen capture of the same filter loaded up in Eastern TZ and in Pacific TZ systems.

[Isomorphic]

You're again hitting the limitations of JSON here, in that there's no way to represent dates even if you cheat a bit by introducing JavaScript expressions, but even then there's no way to represent the concept of a "logical date" or "logical time" value.

You might think that we could tweak the format so that, when dealing with a logical date value, it writes out new Date(y,m,d). But if you try that in the Brazil locale with new Date(2003,9,19) you'll find you get October 18th instead of the expected 19th, because midnight did not exist in Brazil on that day (DST skipped it).

Not to mention that the above strategy wouldn't work for logical time values.

Basically to fully represent logical dates and times you need to either post-process the JSON or embed calls to JavaScript helper methods that are defined outside the JSON as such - and this is what we do for client<->server data delivery.

What we'd recommend you do if you want a timezone-independent way of representing criteria involving dates is to use the default (xmlSchema) format for dates in JSON and add your own post-processing which parses date values. Post-processing is just an unfortunate fact of life with JSON and dates.

We will also look at adding a similar capability as a framework enhancement - essentially we would be introducing a new, not-actually-JSON serialization of AdvancedCriteria that can round-trip such values.

[stonebranch1]

Understood. Thanks for the response.

I was using the XML Schema format and will go back to that as I had it round-tripping the Dates fine since I set a parser that handles the ISO 8601 (XML) date format. The only problem I have with that is the Between operator on the Date introduces the times which then brings back the times and therefore timezones into play causing issues as noted in the other post referenced above.

Is there anyway that when using the XML schema formatting that I can change how the Between values get stored? I basically want to remove the time portion and only have the 2 values stored as Dates the same way for the non-between operators? So basically instead of

        {
            "operator":"iBetweenInclusive", 
            "fieldName":"tdate", 
            "start":"2014-12-01T05:00:00.000", 
            "end":"2015-01-01T04:59:59.999"
        }, 
         {
            "fieldName":"tdate", 
            "operator":"lessThan", 
            "value":"2016-05-01"
        },

Have it stored as:

        {
            "operator":"iBetweenInclusive", 
            "fieldName":"tdate", 
            "start":"2014-12-01", 
            "end":"2015-01-01"
        }, 
         {
            "fieldName":"tdate", 
            "operator":"lessThan", 
            "value":"2016-05-01"
        },

Thanks.

[stonebranch1]

I just discovered another quirk with my current setup.

I am now using the the XML schema encoding so Dates in the filter are stored as "2014-06-01". When a Server fetch is peformed, we handle Dates within the criteria in multiple formats so if it is a Date object or String we handle it and do the fetch returning the matching rows as expected (all good). However, since we are now returning Date field values as Date objects (not String), the client-side filtering ends up removing the records from the list since it appears it does not handle matching a Date object in the record to a Date String in the filter.

For example if I perform the fetch with no filter and get back ALL the data (say 10 records 3 of which have the date field set to "2014-06-01" and then I apply the filter, client side filtering kicks in since the listgrid has all the data and the filtering removes all the records from the list. If I then say call invalidate cache (via a refresh button we display) a server-side fetch is performed and the 3 records are returned and displayed.

Ontop of all this, since we have a Master/Details UI, when a row is selected in the list, we always re-fetch that row and update the cache and show in the form (to make sure we get the latest data). In the scenario above, after the server-side fetch is performed and we select one of the 3 rows (causing the re-fetch and cache update) the row disappears from the listgrid (assuming the client side filtering of that date again) and then is displayed in the form.

I am not sure how I should be handling this or if the client side filtering should be handling it.

Thanks.

[Isomorphic]

I was using the XML Schema format and will go back to that as I had it round-tripping the Dates fine since I set a parser that handles the ISO 8601 (XML) date format.

You keep referring to having "set a parser" - just want to emphasize once again, formatters and parsers configured on DateUtil do not affect JSON serialization or de-serialization.

The only problem I have with that is the Between operator on the Date introduces the times which then brings back the times and therefore timezones into play causing issues as noted in the other post referenced above.

Since serialization always uses UTC, if you parse in UTC, no timezone issue is introduced.

However, since we are now returning Date field values as Date objects (not String), the client-side filtering ends up removing the records from the list since it appears it does not handle matching a Date object in the record to a Date String in the filter.

Criteria objects passed to APIs such as fetchData() should never use Strings for Date values (inclusive of date, time, datetime). Always use actual Date instances and client-side filtering will return to working properly.

[Isomorphic]

The only problem I have with that is the Between operator on the Date introduces the times which then brings back the times and therefore timezones into play causing issues as noted in the other post referenced above.

Since serialization always uses UTC, if you parse in UTC, no timezone issue is introduced.

Let's clarify this further: as we stated above, if you parse in UTC you will have no trouble accurately re-creating the value regardless of timezone.

However, there is a UI-level question here: say a user defines criteria as "between two days" and then another user in a different timezone uses the same criteria. These two users have a different notion of what a "day" is. So what are you expecting?

1. the criteria reflects the day of the person who defined the criteria

OR

2. the criteria reflects the day of the person viewing the criteria

#1 is what the framework currently assumes.

[stonebranch1]

A Date is just a Date regardless of TimeZone so I if I say 2014-06-01 then that should sill be June 1st no matter what timezone you are in since it is referring to the Date in an TimeZone oblivious manner. You said a notion of a "day" but we are talking about a Date, not a Day. I agree a Day has different semantics than a Date.

Let me illustrate the "quirk" and maybe it is fine but here it is...

User in Eastern TimeZone creates a filter that is basically defined as (date between 2014-06-01 & 2014-06-30 OR ( date >= 2014-06-01 AND date <= 2014-06-30)). The OR is really redundant as the between and the AND'd GT & LT are logicially equivalent. I just put them in the same filter so you can see them (see attached image).

This results in the following filter being defined:

{
    "_constructor":"AdvancedCriteria", 
    "operator":"or", 
    "criteria":[
        {
            "operator":"iBetweenInclusive", 
            "fieldName":"tdate", 
            "start":"2014-06-01T04:00:00.000", 
            "end":"2014-07-01T03:59:59.999"
        }, 
        {
            "_constructor":"AdvancedCriteria", 
            "operator":"and", 
            "criteria":[
                {
                    "fieldName":"tdate", 
                    "operator":"greaterOrEqual", 
                    "value":"2014-06-01"
                }, 
                {
                    "fieldName":"tdate", 
                    "operator":"lessOrEqual", 
                    "value":"2014-06-30"
                }
            ]
        }
    ]
}

The user in Eastern TimeZone will see this filter as expected. A user in Pacific Time loads up that same filter and it shows the between shows "2014-05-31" and "2014-06-30" and the GT as "2014-06-01" and the LT as "2014-06-30". These 2 parts of the filter that are Logically equvialent now show different values (see attached images).

We will look further at this and either remove the Between operator from our filtering or I guess live with the "quirk". I was hoping that there might be an option on the filter that I could disable times on the Date value for Between operator but that is probably not an option.

[Isomorphic]

A Date is just a Date regardless of TimeZone so I if I say 2014-06-01 then that should sill be June 1st no matter what timezone you are in since it is referring to the Date in an TimeZone oblivious manner. You said a notion of a "day" but we are talking about a Date, not a Day. I agree a Day has different semantics than a Date.

This is a restatement of what SmartGWT terms a "logical date".

What you refer to as a "quirk" is just behavior #1:

1. the criteria reflects the day of the person who defined the criteria

OR

2. the criteria reflects the day of the person viewing the criteria

Both behaviors make sense in different applications. Consider an application where a user shares a report contained saved criteria with another user. If a PST-based user shares a report to someone in the EU and the data is different, that would very, very obviously be considered a bug in that application.

If the behavior doesn't make sense for your app, yes, disabling the "between" operator would avoid it. Alternatively, if you want a flag to control what happens, that could be done via Feature Sponsorship.

[stonebranch1]

For this particular case, it comes down to the fact should these 2 filters equate to the same thing?

Filter 1: Date >= "2014-06-01" && Date <= "2014-06-30"

Filter 2: Date Between "2014-06-01" && "2014-06-30"

Logically the seem to be the exact same. But as shown, they are not w/r to the AdvancedCriteria and the XML schema encoding.

Anyway, we will deal with this nuance.

Thanks.

[Isomorphic]

For completeness: again, both behaviors are desirable in different applications. See discussion in prior post.

[Isomorphic]

A could of updates:

1. we found one subsystem elsewhere in the framework that was using JSON dateConstructor-style serialization and would have had timezone shift if a user saved data in one TZ and then another user loaded it in another TZ. This promoted the need for timezone-neutral JSON serialization to arguably be a bug, so we added a new mode to JSONEncoder LOGICAL_DATE_CONSTRUCTOR in 4.1, which, as we suggested before, actually writes little JavaScript snippets into the JSON to call SmartClient utility methods. So you can use this instead of having to add post-processing of JSON.

2. we were reviewing the behavior of "betweenInclusive" and realized that per spec, it should not be sending time values for a field of type "date", because we technically do not require that the server representation of this field type even has a notion of time values. So this means the criteria will now be as you expect and show the date value only, and that the selected range will actually shift according to which timezone you use the criteria, reflecting that user's notion of the "day". The behavior for a field of type "datetime" is unchanged - the selected range is what the user defined, regardless of where the criteria is viewed.

[stonebranch1]

Thank You Kindly.