search expression datetime cast incorrect

[Hannes (Neobis)]

When searching records by a datetime field, using a date value as search argument, the following happens:

search arguments:

[('datetime_field_1', '>=', '2013-01-01'), ('datetime_field_1', '<', '2013-01-31'), ]

Is rendered to SQL:

select x.id from "x"
where (x.datetime_field_1 >= '2013-01-01 00:00:00')
AND (x.datetime_field_1 < '2013-01-31 23:59:59')

This is done by code in expression.py (search for "00:00" in the server branch).

There is the following code found:

                    if operator in ('>', '>='):
                        self.__exp[i][2] += ' 00:00:00'
                    elif operator in ('<', '<='):
                        self.__exp[i][2] += ' 23:59:59'

Of course, the problem is caused by the developer who searches with DATE strings instead of DATETIME strings.
OpenERP clearly wants to cast DATE values.
I think this piece of code shouldn't exist there. It should either raise an exception, or, delegate the casting to python / postgres.

Besides the casting-or-not issue, there is a real bug in the openerp-casting.

I think it should be:

                    if operator in ('<', '>='):
                        self.__exp[i][2] += ' 00:00:00'
                    if operator in ('>', '<='):
                        self.__exp[i][2] += ' 23:59:59'

or just:

                    if operator in ('>', '<', '>=', '<='):
                        self.__exp[i][2] += ' 00:00:00'

Think about it:
When searching for records with datetime smaller than (<) 2013-01-05 you don't expect records with datetime 2013-01-05 23:00:00, do you?
And when searching for records with datetime larger than (>) 2013-01-05 you don't expect records with datetime 2013-01-05 01:00:00, do you?

I think a lot of software cast DATE 2013-01-05 to DATETIME 2013-01-05 00:00:00.

At the same time I would like to warn you, because this is a very fundamental change which might affect various users in an unexpected way.

I think the best solution is a step-by-step approach through various OpenERP server versions:
1st: send WARNINGS to the server log when casting takes place
2nd: make the OpenERP cast DEPRECATED
3rd: raise Exceptions (if possible only for developers)
4th: remove entire piece of code.

I hope I could help

Hannes Smit

[Hannes (Neobis) (nb2)]

allright, i still think it is a good idea to put on the TODO list.

[Jignesh Rathod(OpenERP) (jir-openerp)]

Hello Hannes ,

I have checked your issue and I agree with you.
when we input datetime search like ....

x.datetime_field_1 < '2013-01-01'
then
x.datetime_field_1 < '2013-01-01 00:00:00' instead of 23:59:59.

For criteria 2 :

x.datetime_field_1 > '2013-01-01'

then it should be ... x.datetime_field_1 > '2013-01-01 23:59:59' instead of 00:00:00 .

Thanks for reporting.