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

[nilay khatri]

I don't think this was fixed. Though I am not sure, will have to cross
check.

There was AM PM issue and this issue. AM PM issue was fixed but this one
was not fixed as I remember.

On Fri, May 18, 2018 at 3:51 PM, Alan Lord <alanslists at gmail.com> wrote:

> 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/
>>
>>
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20180518/3cfbc066/attachment.html>