[Vtigercrm-developers] vt65: Using "Between" dates on datetime fields seems to be broken.

[Alan Lord]

If I change the user's "Calendar Hour Format" preferences from 12hr to 
24hr, then the *between* query works...

Anyone remember this being fixed at some point in vt65?

> Fri May 18 10:18:22 2018,816 [1069] DEBUG VT - Entering getValidDBInsertDateValue(20-05-2017) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getValidDBInsertDateValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Entering getDBInsertDateValue(2017-05-20 00:00:00) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getDBInsertDateValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Entering getDBInsertTimeValue(2017-05-20 00:00:00) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getDBInsertTimeValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Entering getValidDBInsertDateValue(20-05-2018) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getValidDBInsertDateValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Entering getDBInsertDateValue(2018-05-20 00:00:00) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getDBInsertDateValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Entering getDBInsertTimeValue(2018-05-20 00:00:00) method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Exiting getDBInsertTimeValue method ...
> Fri May 18 10:18:22 2018,817 [1069] DEBUG VT - Prepared sql query being executed : SELECT vtiger_jobseekers.jsfirstname, vtiger_jobseekers.jslastname, vtiger_jobseekers.jsphone, vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime, vtiger_jobseekers.jobseekersid FROM vtiger_jobseekers  INNER JOIN vtiger_crmentity ON vtiger_jobseekers.jobseekersid = vtiger_crmentity.crmid  WHERE vtiger_crmentity.deleted=0 AND   (  (( CAST(vtiger_crmentity.createdtime AS DATE) BETWEEN '2017-05-19 23:00:00' AND '2018-05-19 23:00:00') )) AND vtiger_jobseekers.jobseekersid > 0 ORDER BY modifiedtime DESC LIMIT 0,21


On 18/05/18 10:39, Alan Lord wrote:
> Has anyone seen this before and can point me to the fix? I couldn't find 
> an obvious reference to this on Gitlab.
> 
> This is the same filter. I start with simple condition such as Created 
> Time is *Before* 18/05/2017.
> 
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Entering 
>> getValidDBInsertDateValue(2017-05-18) method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Exiting 
>> getValidDBInsertDateValue method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Entering 
>> getDBInsertDateValue(2017-05-18 23:59:58) method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Exiting 
>> getDBInsertDateValue method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Entering 
>> getDBInsertTimeValue(2017-05-18 23:59:58) method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Exiting 
>> getDBInsertTimeValue method ...
>> Fri May 18 09:16:51 2018,359 [8905] DEBUG VT - Prepared sql query 
>> being executed : SELECT vtiger_jobseekers.jsfirstname, 
>> vtiger_jobseekers.jslastname, vtiger_jobseekers.jsphone, 
>> vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime, 
>> vtiger_jobseekers.jobseekersid FROM vtiger_jobseekers  INNER JOIN 
>> vtiger_crmentity ON vtiger_jobseekers.jobseekersid = 
>> vtiger_crmentity.crmid  WHERE vtiger_crmentity.deleted=0 AND   (  (( 
>> vtiger_crmentity.createdtime < '2017-05-18 22:59:58') )) AND 
>> vtiger_jobseekers.jobseekersid > 0 ORDER BY modifiedtime DESC LIMIT 0,21
> 
> 
> Then if I edit my filter and ask for the Created Time *Between* 
> 18/05/2017 and 18/05/2018, it breaks. Look at what is being passed into 
> the getValidDBInsertDateValue() function compared with above. Note there 
> is no exit logged from the getValidDBInsertDateValue function or any 
> kind of formatted date passed to the Query:
> 
>> Fri May 18 09:19:02 2018,360 [8809] DEBUG VT - Entering 
>> getValidDBInsertDateValue(19-05-2017 12:00 AM) method ...
>> Fri May 18 09:19:02 2018,360 [8809] DEBUG VT - Entering 
>> getValidDBInsertDateValue(19-05-2018 12:00 AM) method ...
>> Fri May 18 09:19:02 2018,361 [8809] DEBUG VT - Prepared sql query 
>> being executed : SELECT vtiger_jobseekers.jsfirstname, 
>> vtiger_jobseekers.jslastname, vtiger_jobseekers.jsphone, 
>> vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime, 
>> vtiger_jobseekers.jobseekersid FROM vtiger_jobseekers  INNER JOIN 
>> vtiger_crmentity ON vtiger_jobseekers.jobseekersid = 
>> vtiger_crmentity.crmid  WHERE vtiger_crmentity.deleted=0 AND   (  (( 
>> vtiger_crmentity.createdtime BETWEEN '' AND '') )) AND 
>> vtiger_jobseekers.jobseekersid > 0 ORDER BY modifiedtime DESC LIMIT 0,21
> 
> TIA
> 
> Al
> 
> _______________________________________________
> http://www.vtiger.com/
>