Skip to content

Error displaying and filtering OData DataGrid with ISO UTC dates #607

@rockgecko-development

Description

@rockgecko-development

Bug Report

Package versions:

devexteme version: "^21.1.3"
devextreme-react version: "^21.1.3"

Steps to reproduce:
As in DevExpress/devextreme-angular#969 , my OData endpoint returns dates in ISO format, eg
"start": "2021-09-16T01:06:41.1234Z" and we'd like to show and filter the data in the user's local time. Following the solution in that issue, I used deserializeDates: false in the OData store options, and dateSerializationFormat={null}, as below:

myDataSource = new ODataStore({
url: myUrl,
key: 'myId',
deserializeDates: false,
version: 4,
});

...

<DataGrid
dataSource={myDataSource}
...
// @ts-ignore
dateSerializationFormat={null}
>
 <RemoteOperations
    filtering={true}
    paging={true}
    sorting={true}
    summary={true}
    grouping={true}
    groupPaging={true}
/>
<FilterRow visible />
...
<Column
        dataField='start'
        defaultSortIndex={0}
        defaultSortOrder='desc'
        dataType='datetime'
        format="d/MM/yyyy HH:mm"
                      />
</DataGrid>

This works, everything displays correctly in local time, except for the date filter picker. Values from the picker are passed to the API directly with no TZ conversion, meaning the user is effectively selecting a UTC date time in the picker.
I am in GMT+10. If I select greater than "1-9-21 16:40" in the picker,
$filter: start ge 2021-09-01T16:41:00Z is sent to the API. Instead, I expect
$filter: start ge 2021-09-01T06:41:00Z to be sent.

If I remove the dateSerializationFormat={null} attribute from the DataGrid, or specify a custom format there, the conversion works!

$filter: start ge '2021-09-01T06:41:00.000Z'

However, the date is now surrounded with single quotes, which causes the API to throw a 500 with

Microsoft.OData.Core: A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

Current behavior:
I am in GMT+10. If I select "1-9-21 16:40" in the picker, $filter: start ge 2021-09-01T16:41:00Z is sent to the API.

Expected behavior:
The selected date should be converted to UTC, and sent without quotation marks, eg $filter: start ge 2021-09-01T06:41:00Z should be sent.

An alternative fix for this issue might be to revisit this decision:

Stores can parse date-time values in ISO8601 format (for example, "2016-07-13T16:05:00.000Z") or Microsoft format (for instance, "/Date(1198908717056)/"). In the first case, the store ignores the timezone modifier (usually Z) when parsing the value. In the second case, the store adds the time-zone offset to the value according to the client's time-zone.
ref https://js.devexpress.com/Documentation/ApiReference/Data_Layer/ODataContext/Configuration/#deserializeDates

Not sure what the reasoning is to ignore the TZ, given that the standard UI components do correctly convert Z (utc) to local when rendering ISO dates.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions