OroPlatform Forums

Covering OroPlatform topics, including community updates and company announcements.

Forums Forums OroPlatform OroPlatform – Installation/Technical Issues or Problems Date Filters not properly working for Datagrids

This topic contains 2 replies, has 2 voices, and was last updated by  shreyas 4 years, 10 months ago.

Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.

  • Creator
    Topic
  • #35425

    shreyas
    Participant

    Hi Team ,
    We are facing issue while filtering Dates in Datagrids (currently working in FR locale , but this issue exists even for EN locale also) .

    The issue is if we choose Dates Between : then filter will select previous Dates in its queries from that of chosen Dates.
    Ex:
    Tested for : Opportunities Entity index page
    Start : 19 oct. 2017 End: 21 oct. 2017
    result will be fetched from 2017-10-18 22:00:00 to 2017-10-20 22:00:00 . i think there is some timezone issue during filtering. The below details i got from logs while triggered the Date filter btn .

    If you notice above , the 1st query which is to get count will take parameters correctly (i.e start as 2017-10-19 00:00:00 & end as 2017-10-21 00:00:00), where in the second Qry used to fetch the result – parameters used for start & end dates are different (i.e start as 2017-10-18 22:00:00 & end as 2017-10-20 22:00:00).

    i tried looking at the files CountQueryOptimizationListener.php & OrmDatasourceAclListener.php but dint find much from them.

    i even tried typecasting the date field used in filters option of Datagrid with DATE but it too dint work .

    This is the issue for if select Dates between ,Dates not between option & similar kind of date changes issue occurs for rest of Date filter options also.

    I tried looking at both FilterBundle & DataGrid Bundle for any modifications that i can do ( like: FilterBundle/Filter/AbstractDateFilter.php & FilterBundle\Utils\DateFilterModifier.php & some JS files under Filter Directory) . but could not get much idea on how to go with it.

    If we check for any of DateTime Filter also,the similar issue occurs there as well.

    Please find below reference for localization details:
    Localization Details 1
    Localization Details 2

    Application Details for reference:

    Application Version: 2.3
    Application Edition: Community
    2. Environment related information:
    OS, name and version: CentOS 7.3
    Web server, name and version: Apache 2.4
    Database, name and version: MySQL 5.7
    PHP, version: 7.1

    The same issue is seen even in demo.orocrm.com

    Please need help in how to solve this issue.Is there any link that shows how the filters work by dynamically building the queries so that i can do some modification to those dates to fix issue.

    Thanks & Regards,
    Shreyas S

Viewing 2 replies - 1 through 2 (of 2 total)
  • Author
    Replies
  • #35426

    Yurii Muratov
    Participant

    Hello, shreyas.
    The given queries works correct.

    All dates in application are stored in UTC time zone.https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/LocaleBundle/Resources/doc/reference/locale-settings.md#time-zone

    So, we should change the time you select to GMT timezone.

    As i can see, you have select Europe/Paris, and as parameters you select 19 oct. 2017 and 21 oct. 2017.
    This two dates in GMT2 period. So, we should decrease 2 hours. That is why you get 2017-10-18 22:00:00 and 2017-10-20 22:00:00.

    Time when you made a screenshot with locale settings was made after 29 oct 2017 03:00. That is why at the screenshot you can see UTC+1 string (GMT1).

    The both queries was sent to db with correct dates 2017-10-18 22:00:00 and 2017-10-20 22:00:00.

    We have a small issue with logging queries that use additional count queries. They both use the same parameters. But, this parameters logs before conversation. That is why you can see not converted values for the first query and converted for the second query.

    The conversation was made by UTCDateTimeType https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/LocaleBundle/DoctrineExtensions/DBAL/Types/UTCDateTimeType.php#L26

    #35427

    shreyas
    Participant

    Hi Yurii Muratov,

    Thanks a lot for your quick reply. Sorry for delayed response .I had few doubts which i would like to clear from it.

    My concern in this Date filter issue is that irrespective of the timezone we have selected , at last it gets saved in UTC format & retrieves in whatever timezone we have selected. So, for example i created some opportunities in Application which contains Expected close Date.Thus once i created an opportunity in Application & checked for Dates in DB . The Dates saved in DB & that created in Application the same.

    Please find the observation below ( i have chosen for EN locale now):
    List of all opportunities below:

    List of Opportunities

    Now i use Date filter Between option for Expected close time filtering from Oct 19, 2017 till Oct 21, 2017 to be as follows:

    Filter for Expected Close Date 19-oct till 21 oct

    Now if you notice the above filtered results , it does not contain result related to Oct 21,2017 . which i feel should have been listed in these filtered results. Please let me know if i am wrong about it.

    Please find the DB related information for these data:

    DB information for Opportunity Data

    Locale Information is as follows:
    Localization Info

    Also logs just for reference:

    This is similar issue which we faced with French locale being selected also (explained in above post). Please let me know if this is an issue or not. other doubt was that when the field used for filtering is of Date type why is it Date Time being taken into consideration here.

    Thanks & Regards,
    Shreyas S

Viewing 2 replies - 1 through 2 (of 2 total)

The forum ‘OroPlatform – Installation/Technical Issues or Problems’ is closed to new topics and replies.

Back to top

You will be redirected to [title]. Would you like to continue?

Yes No

You will be redirected to [title]. Would you like to continue?

Yes No