Date Filters not properly working for Datagrids

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 .

[2017-10-31 14:01:39] event.DEBUG: Notified event "oro.entity.count_query.optimize" to listener "Oro\Bundle\FormBundle\EventListener\CountQueryOptimizationListener::onOptimize". [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] doctrine.DEBUG: SELECT count(o0_.id) AS sclr_0 FROM orocrm_sales_opportunity o0_ WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ["2017-10-19 00:00:00","2017-10-21 00:00:00",["lost","won"]] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] event.DEBUG: Notified event "oro_datagrid.orm_datasource.result.before" to listener "Oro\Bundle\DataGridBundle\EventListener\OrmDatasourceAclListener::onResultBefore". [] [] [2017-10-31 14:01:39] doctrine.DEBUG: SELECT o0_.id AS id_0, o0_.name AS name_1, o0_.created_at AS created_at_2, o0_.updated_at AS updated_at_3, o0_.close_date AS close_date_4, o0_.probability AS probability_5, o0_.budget_amount_value AS budget_amount_value_6, o0_.close_revenue_value AS close_revenue_value_7, o0_.budget_amount_currency AS budget_amount_currency_8, o0_.close_revenue_currency AS close_revenue_currency_9, o0_.status_id AS sclr_10, CONCAT_WS(' ', o1_.first_name, o1_.last_name) AS sclr_11, o1_.id AS id_12, o2_.label AS label_13, o3_.email AS email_14, o4_.id AS id_15, CONCAT_WS(' ', o4_.first_name, o4_.last_name) AS sclr_16, o5_.name AS name_17, o0_.ac_contact_count AS ac_contact_count_18, o0_.ac_contact_count_in AS ac_contact_count_in_19, o0_.ac_contact_count_out AS ac_contact_count_out_20, o0_.ac_last_contact_date AS ac_last_contact_date_21, o0_.ac_last_contact_date_in AS ac_last_contact_date_in_22, o0_.ac_last_contact_date_out AS ac_last_contact_date_out_23, DATEDIFF(CURRENT_DATE, o0_.ac_last_contact_date) AS sclr_24, o0_.organization_id AS sclr_25, o0_.user_owner_id AS sclr_26 FROM orocrm_sales_opportunity o0_ LEFT JOIN orocrm_contact o1_ ON o0_.contact_id = o1_.id AND (o1_.organization_id = 1) LEFT JOIN orocrm_sales_opport_close_rsn o2_ ON o0_.close_reason_name = o2_.name LEFT JOIN orocrm_contact_email o3_ ON o1_.id = o3_.owner_id AND (o3_.is_primary = 1) LEFT JOIN oro_user o4_ ON o0_.user_owner_id = o4_.id LEFT JOIN orocrm_sales_customer o6_ ON o0_.customer_association_id = o6_.id LEFT JOIN orocrm_account o5_ ON o6_.account_id = o5_.id AND (o5_.organization_id = 1) WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ORDER BY o0_.created_at DESC, o0_.id DESC LIMIT 25 OFFSET 0 ["2017-10-18 22:00:00","2017-10-20 22:00:00",["lost","won"]]

1 2 3 4 5 6 7 8

[2017-10-31 14:01:39] event.DEBUG: Notified event "oro.entity.count_query.optimize" to listener "Oro\Bundle\FormBundle\EventListener\CountQueryOptimizationListener::onOptimize". [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] doctrine.DEBUG: SELECT count(o0_.id) AS sclr_0 FROM orocrm_sales_opportunity o0_ WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ["2017-10-19 00:00:00","2017-10-21 00:00:00",["lost","won"]] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access. [] [] [2017-10-31 14:01:39] event.DEBUG: Notified event "oro_datagrid.orm_datasource.result.before" to listener "Oro\Bundle\DataGridBundle\EventListener\OrmDatasourceAclListener::onResultBefore". [] [] [2017-10-31 14:01:39] doctrine.DEBUG: SELECT o0_.id AS id_0, o0_.name AS name_1, o0_.created_at AS created_at_2, o0_.updated_at AS updated_at_3, o0_.close_date AS close_date_4, o0_.probability AS probability_5, o0_.budget_amount_value AS budget_amount_value_6, o0_.close_revenue_value AS close_revenue_value_7, o0_.budget_amount_currency AS budget_amount_currency_8, o0_.close_revenue_currency AS close_revenue_currency_9, o0_.status_id AS sclr_10, CONCAT_WS(' ', o1_.first_name, o1_.last_name) AS sclr_11, o1_.id AS id_12, o2_.label AS label_13, o3_.email AS email_14, o4_.id AS id_15, CONCAT_WS(' ', o4_.first_name, o4_.last_name) AS sclr_16, o5_.name AS name_17, o0_.ac_contact_count AS ac_contact_count_18, o0_.ac_contact_count_in AS ac_contact_count_in_19, o0_.ac_contact_count_out AS ac_contact_count_out_20, o0_.ac_last_contact_date AS ac_last_contact_date_21, o0_.ac_last_contact_date_in AS ac_last_contact_date_in_22, o0_.ac_last_contact_date_out AS ac_last_contact_date_out_23, DATEDIFF(CURRENT_DATE, o0_.ac_last_contact_date) AS sclr_24, o0_.organization_id AS sclr_25, o0_.user_owner_id AS sclr_26 FROM orocrm_sales_opportunity o0_ LEFT JOIN orocrm_contact o1_ ON o0_.contact_id = o1_.id AND (o1_.organization_id = 1) LEFT JOIN orocrm_sales_opport_close_rsn o2_ ON o0_.close_reason_name = o2_.name LEFT JOIN orocrm_contact_email o3_ ON o1_.id = o3_.owner_id AND (o3_.is_primary = 1) LEFT JOIN oro_user o4_ ON o0_.user_owner_id = o4_.id LEFT JOIN orocrm_sales_customer o6_ ON o0_.customer_association_id = o6_.id LEFT JOIN orocrm_account o5_ ON o6_.account_id = o5_.id AND (o5_.organization_id = 1) WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ORDER BY o0_.created_at DESC, o0_.id DESC LIMIT 25 OFFSET 0 ["2017-10-18 22:00:00","2017-10-20 22:00:00",["lost","won"]]

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

Topic

[Yurii MuratovParticipant]

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

[shreyasParticipant]

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:

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

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:

Locale Information is as follows:

Also logs just for reference:

Shell

[2017-11-03 16:33:39] doctrine.DEBUG: SELECT count(o0_.id) AS sclr_0 FROM orocrm_sales_opportunity o0_ WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ["2017-10-19 00:00:00","2017-10-21 00:00:00",["lost","won"]] [] [2017-11-03 16:33:39] event.DEBUG: Notified event "oro_datagrid.orm_datasource.result.before" to listener "Oro\Bundle\DataGridBundle\EventListener\OrmDatasourceAclListener::onResultBefore". [] [] [2017-11-03 16:33:39] doctrine.DEBUG: SELECT o0_.id AS id_0, o0_.name AS name_1, o0_.created_at AS created_at_2, o0_.updated_at AS updated_at_3, o0_.close_date AS close_date_4, o0_.probability AS probability_5, o0_.budget_amount_value AS budget_amount_value_6, o0_.close_revenue_value AS close_revenue_value_7, o0_.budget_amount_currency AS budget_amount_currency_8, o0_.close_revenue_currency AS close_revenue_currency_9, o0_.status_id AS sclr_10, CONCAT_WS(' ', o1_.first_name, o1_.last_name) AS sclr_11, o1_.id AS id_12, o2_.label AS label_13, o3_.email AS email_14, o4_.id AS id_15, CONCAT_WS(' ', o4_.first_name, o4_.last_name) AS sclr_16, o5_.name AS name_17, o0_.ac_contact_count AS ac_contact_count_18, o0_.ac_contact_count_in AS ac_contact_count_in_19, o0_.ac_contact_count_out AS ac_contact_count_out_20, o0_.ac_last_contact_date AS ac_last_contact_date_21, o0_.ac_last_contact_date_in AS ac_last_contact_date_in_22, o0_.ac_last_contact_date_out AS ac_last_contact_date_out_23, DATEDIFF(CURRENT_DATE, o0_.ac_last_contact_date) AS sclr_24, o0_.organization_id AS sclr_25, o0_.user_owner_id AS sclr_26 FROM orocrm_sales_opportunity o0_ LEFT JOIN orocrm_contact o1_ ON o0_.contact_id = o1_.id AND (o1_.organization_id = 1) LEFT JOIN orocrm_sales_opport_close_rsn o2_ ON o0_.close_reason_name = o2_.name LEFT JOIN orocrm_contact_email o3_ ON o1_.id = o3_.owner_id AND (o3_.is_primary = 1) LEFT JOIN oro_user o4_ ON o0_.user_owner_id = o4_.id LEFT JOIN orocrm_sales_customer o6_ ON o0_.customer_association_id = o6_.id LEFT JOIN orocrm_account o5_ ON o6_.account_id = o5_.id AND (o5_.organization_id = 1) WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ORDER BY o0_.created_at DESC, o0_.id DESC LIMIT 25 OFFSET 0 ["2017-10-18 18:30:00","2017-10-20 18:30:00",["lost","won"]] []

1 2 3

[2017-11-03 16:33:39] doctrine.DEBUG: SELECT count(o0_.id) AS sclr_0 FROM orocrm_sales_opportunity o0_ WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ["2017-10-19 00:00:00","2017-10-21 00:00:00",["lost","won"]] [] [2017-11-03 16:33:39] event.DEBUG: Notified event "oro_datagrid.orm_datasource.result.before" to listener "Oro\Bundle\DataGridBundle\EventListener\OrmDatasourceAclListener::onResultBefore". [] [] [2017-11-03 16:33:39] doctrine.DEBUG: SELECT o0_.id AS id_0, o0_.name AS name_1, o0_.created_at AS created_at_2, o0_.updated_at AS updated_at_3, o0_.close_date AS close_date_4, o0_.probability AS probability_5, o0_.budget_amount_value AS budget_amount_value_6, o0_.close_revenue_value AS close_revenue_value_7, o0_.budget_amount_currency AS budget_amount_currency_8, o0_.close_revenue_currency AS close_revenue_currency_9, o0_.status_id AS sclr_10, CONCAT_WS(' ', o1_.first_name, o1_.last_name) AS sclr_11, o1_.id AS id_12, o2_.label AS label_13, o3_.email AS email_14, o4_.id AS id_15, CONCAT_WS(' ', o4_.first_name, o4_.last_name) AS sclr_16, o5_.name AS name_17, o0_.ac_contact_count AS ac_contact_count_18, o0_.ac_contact_count_in AS ac_contact_count_in_19, o0_.ac_contact_count_out AS ac_contact_count_out_20, o0_.ac_last_contact_date AS ac_last_contact_date_21, o0_.ac_last_contact_date_in AS ac_last_contact_date_in_22, o0_.ac_last_contact_date_out AS ac_last_contact_date_out_23, DATEDIFF(CURRENT_DATE, o0_.ac_last_contact_date) AS sclr_24, o0_.organization_id AS sclr_25, o0_.user_owner_id AS sclr_26 FROM orocrm_sales_opportunity o0_ LEFT JOIN orocrm_contact o1_ ON o0_.contact_id = o1_.id AND (o1_.organization_id = 1) LEFT JOIN orocrm_sales_opport_close_rsn o2_ ON o0_.close_reason_name = o2_.name LEFT JOIN orocrm_contact_email o3_ ON o1_.id = o3_.owner_id AND (o3_.is_primary = 1) LEFT JOIN oro_user o4_ ON o0_.user_owner_id = o4_.id LEFT JOIN orocrm_sales_customer o6_ ON o0_.customer_association_id = o6_.id LEFT JOIN orocrm_account o5_ ON o6_.account_id = o5_.id AND (o5_.organization_id = 1) WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ORDER BY o0_.created_at DESC, o0_.id DESC LIMIT 25 OFFSET 0 ["2017-10-18 18:30:00","2017-10-20 18:30:00",["lost","won"]] []

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

[Author]

Replies