appending times to dates when searching datetime doesn't take timezone into account

[Holger Brunn (Therp)]

In expression.py, the server appends ' 00:00:00' or ' 23:59:59' to dates if the field to be searched is of type datetime.

This gives confusing results for dates on day boundaries. Consider me searching
[('date', '<=', '2013-11-27')]
in timezone Europe/Amsterdam
which is rewritten in sql to
date <= '2013-11-27 23:59:59'
and a record with date 2013-11-27 23:30:00 UTC be found, even though it's 2013-11-28 00:30:00 CET.

So I think if we mess with the dates at all, we should convert the searched date from the user's timezone to UTC and search for the result or result+23:59:59.

I'll link a fix for ocb-server 6.1, but I'll be happy to provide merge requests for trunk and 7.0 proper if this is considered a bug.

[Bill Ennals (bennals)]

I would certainly consider it a bug and, living in Australia, would welcome any timezone quirk (read bug) fixes.

Thanks Holger,

Bill.

[Harri Luuppala (harri-luuppala)]

Holger

I am doing checking for issues I should know before my installation of OE is in full production.

FYI: OE is using UTC in the DB like you might know and this is as it should be. It also means that server OS has to use same (as they mostly do). See

In theory any usage of date or time in OE should have:
1. Correct "datetime" in / out handling: UTC -> Local TZ, Local TZ -> UTC.
2. There should be handling for "datetime" in reports, screens, interfaces so that user gets correct TZ, but also Customers, Suppliers, Multi-Companies, Banks (e.g. eInvoicing) etc who might receive data gets correct TZ.

You are not alone:
https://bugs.launchpad.net/openobject-addons/+bug/1179893
https://bugs.launchpad.net/openobject-server/+bug/1155843
https://bugs.launchpad.net/openobject-addons/+bug/1219845

My proposal is that you link your Bug to these.

And discussions about these TX issues:
http://help.openerp.com/question/31908/how-to-get-attendance-be-assigned-to-the-correct-day-for-anyone-who-is-not-in-the-utc-timezone/

http://help.openerp.com/question/20105/timezone-issue-in-openerp-70-version/

http://help.openerp.com/question/17404/is-there-a-workaround-for-the-bug-where-timesheet-entries-use-incorrect-timezone/

One part solution here:
http://help.openerp.com/question/17404/is-there-a-workaround-for-the-bug-where-timesheet-entries-use-incorrect-timezone/

TZ is an old source of issue for OE - now TZ issues are not anymore trivial for average designers to realize.
http://forum.openerp.com/forum/topic34520.html

Hopefully this helps you Holger to see picture and hopefully I am totally wrong with this educated guess that this TZ issue is lurkingg here and ether.

PS: TZ, Character Set, Currencies, and other i18n & L10n issues are hard to solve one and for all. Most of designers does understand their own and OS defaults, but it is not easy to see the big picture. Starting guide for the issues: http://en.wikipedia.org/wiki/Internationalization_and_localization

PS2: Luckily Linus Thorvalds (Linux) & Monty Widenius (MySQL, MariaSQL) are both boldy & bi-langual Finns, so they have done more than average developer to boost the big journey to have some day i18n and L10n solved. Only 30 years has passed to this level :-)

BR Harri from snowy and cold Finland (-20 Degrees Celsius) to hot Australia :-) and Holger to warm NL

[Martin Collins (mkc-steadfast)]

Holger, your fix only affects datetimes with no time, but those *with* time are also not converted to UTC in search domains.
Also, context_timestamp() converts from UTC to the client's tz, which is the wrong way round.

I attach my attempt at fixing both issues. It will affect any datetime on the right of a domain that is parsed by this code. I would hope that any which are already in UTC will have 'UTC' in their tz or have no tz in their context (can anyone confirm this?) so will not be converted.

For 6.1 only. (Something worrying has happened in 8)

[Holger Brunn (Therp) (hbrunn)]

Have a look at this discussion: https://github.com/odoo/odoo/pull/2914

The result is this module https://github.com/OCA/web/pull/45 which should be simple to port to 6.1 (or 8.0 for that matter). This seems to be the better way to go.

[Martin Collins (mkc-steadfast)]

So the bug is in the client (all datetimes sent to the server should already be in UTC).
Since we are still using the GTK client I suppose we'll have to live with it until we move to 8 some time next year.