[Openerp-community] How are you dealing with time zone issues on datetime fields

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Openerp-community] How are you dealing with time zone issues on datetime fields

Mario Arias
Hi,

We are very interested in knowing how are you, partners and community members, dealing with timezone issues on datetime fields, at least in the most common case where customers have all their operations in one single time zone...

Have you hard-coded the time delta in defaults and filters, created patches to base modules handling datetime domains, created extra DATE only fields to avoid the issue, replaced DATETIME fields with DATE ones, or what ?


For those that haven't taken the time to check, currently Odoo/OpenERP doesn't produce accurate results when you use filters or generate reports based on datetime fileds...

Reports for same day will show different results depending on the local time you generate them, some record from previous or next day will be included in today's results while others will be excluded, ....

If your time zone is GMT-5:00, create one POS order before 19:00 (local time) and then one after 19:00.  Then run the Sales Details report and be surprised... Want even more surprises...  then do this for two days and launch the report in different times of the day...   you will see it changing results...  !!   Great, isn't it ???   ;-)


Please, share your experiences here.

On our side, we have it all working now, just by fixing original Odoo modules...

What we did:
  • Define context_to_utc_timestamp, inverse function to context_timestamp...  PR : https://github.com/odoo/odoo/pull/2914
  • Update PR #2776 from Holger, to use "context_to_utc_timestamp" instead of "context_timestamp"
  • For filters in views, use domain tuples like ('date','<=',context_today().strftime('%%Y-%%m-%%d').  This is important, as filter date should be based on user's context.  Also important, do NOT add time part, as this will be handled by previous PR to match user's timezone !!
  • If you want time deltas, then use something like (context_today()+relativedelta(days=-1)).strftime('%Y-%m-%d')
  • Finally, if you need "replace", it is not defined for dates.  We made a PR for that too...  https://github.com/odoo/odoo/pull/2915

Using previous fixes, results are both CONSISTEN  and CORRECT from user's point of view, while still being saved as UTC timestamps, as long as all records come from the same time zone...

If you need results to be consistent across different time zones, then you can only do it by defining new DATE only fields and filter on them...


So, as long as OpenERP S.A. doesn't provide an official way to really FIX this problem, we can at least keep our customers happy...


Regards,
-Mario

_______________________________________________
Mailing list: https://launchpad.net/~openerp-community
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-community
More help   : https://help.launchpad.net/ListHelp
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Openerp-community] How are you dealing with time zone issues on datetime fields

Ferdinand Gassauer
On 2014-10-09 22:03, Mario Arias wrote:

for various tables I have added a period_id like

    def _period_id(self, cr, uid, ids, name, arg, context):
         result = {}
         for move in self.browse(cr, uid, ids):
             period_ids= self.pool.get('account.period').search(cr,uid,[('company_id','=',move.company_id.id),('date_start','<=',move.date_expected),('date_stop','>=',move.date_expected ),('special','!=', True)], context=context)

             if len(period_ids):
                 result[move.id] = period_ids[0]

         return result

or
* a related field
or
* a date without time field

IMO most queries will done with period or fiscal year as domain and this should be easy to accomplish


I will add a fiscalyear_id,  to allow fast searching for data of companies with  fiscal year != calendar year.
"This Year" does not return usefull data for such companies
or to allow to group by fiscal year.


Hi,

We are very interested in knowing how are you, partners and community members, dealing with timezone issues on datetime fields, at least in the most common case where customers have all their operations in one single time zone...

Have you hard-coded the time delta in defaults and filters, created patches to base modules handling datetime domains, created extra DATE only fields to avoid the issue, replaced DATETIME fields with DATE ones, or what ?


For those that haven't taken the time to check, currently Odoo/OpenERP doesn't produce accurate results when you use filters or generate reports based on datetime fileds...

Reports for same day will show different results depending on the local time you generate them, some record from previous or next day will be included in today's results while others will be excluded, ....

If your time zone is GMT-5:00, create one POS order before 19:00 (local time) and then one after 19:00.  Then run the Sales Details report and be surprised... Want even more surprises...  then do this for two days and launch the report in different times of the day...   you will see it changing results...  !!   Great, isn't it ???   ;-)


Please, share your experiences here.

On our side, we have it all working now, just by fixing original Odoo modules...

What we did:
  • Define context_to_utc_timestamp, inverse function to context_timestamp...  PR : https://github.com/odoo/odoo/pull/2914
  • Update PR #2776 from Holger, to use "context_to_utc_timestamp" instead of "context_timestamp"
  • For filters in views, use domain tuples like ('date','&lt;=',context_today().strftime('%%Y-%%m-%%d').  This is important, as filter date should be based on user's context.  Also important, do NOT add time part, as this will be handled by previous PR to match user's timezone !!
  • If you want time deltas, then use something like (context_today()+relativedelta(days=-1)).strftime('%Y-%m-%d')
  • Finally, if you need "replace", it is not defined for dates.  We made a PR for that too...  https://github.com/odoo/odoo/pull/2915

Using previous fixes, results are both CONSISTEN  and CORRECT from user's point of view, while still being saved as UTC timestamps, as long as all records come from the same time zone...

If you need results to be consistent across different time zones, then you can only do it by defining new DATE only fields and filter on them...


So, as long as OpenERP S.A. doesn't provide an official way to really FIX this problem, we can at least keep our customers happy...


Regards,
-Mario


_______________________________________________
Mailing list: https://launchpad.net/~openerp-community
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-community
More help   : https://help.launchpad.net/ListHelp


-- 
Ferdinand Gassauer
Eigentümer
ChriCar Bet. u. Ber. GmbH

_______________________________________________
Mailing list: https://launchpad.net/~openerp-community
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-community
More help   : https://help.launchpad.net/ListHelp
Loading...