Needing standard/good functions for date/time output in user's timezone for account reports

[Brett Wilkins]

I've run into a problem of not having correct timezones applied when generating reports. This is partly the fault of the server's timezone being forced to UTC, but also due to the lack of availability of easy-to-use functions in the templates used to generate these reports.

What I have done as a proof of concept is to add "import pytz" into openobject-server/openerp/report/report_sxw.py, then add pytz and datetime into the localcontext member variable in rmlparse.

---- DIFF ----

index 27bd5fa..ad2c341 100644
--- a/openerp/report/report_sxw.py
+++ b/openerp/report/report_sxw.py
@@ -26,6 +26,7 @@ from datetime import datetime
 import os
 import re
 import time
+import pytz
 from interface import report_rml
 import preprocess
 import logging
@@ -168,6 +169,8 @@ class rml_parse(object):
             'setHtmlImage' : self.set_html_image,
             'strip_name' : self._strip_name,
             'time' : time,
+ 'pytz': pytz,
+ 'datetime': datetime,
             'display_address': self.display_address,
             # more context members are setup in setCompany() below:
             # - company_id
---- DIFF ----

This change allows me to call pytz and datetime from within the report templates (most specifically I've been looking at the internal header/footer templates for companies). I find this to be preferable over formatLang and time.strftime calls, which do not really allow for timezone changes. formatLang is something of an exception here - it is meant to take a date or datetime string of a particular format and correct the timezone for it, however this breaks easily, and seems superfluous.

What I use currently for correct date and time in the templates are:
Date: [[ datetime.now(python.timezone(tz)).strftime('%Y-%m-%d') ]]
Time: [[ datetime.now(python.timezone(tz)).strftime('%H:%M') ]]

What I think would be much better is easy access to standard objects/functions that will output dates, times and datetimes in the user's timezone. So that we might call them with something like:
Date: [[ utime.today() ]]
Time: [[ utime.localtime() ]]
Datetime: [[ utime.now() ]]

I would also like the flexibility to provide my own strftime string.
I have no attachment to these names for functions/namespaces/objects.

[Brett Wilkins (brettwilkins)]

I would also like to add that these functions may be useful elsewhere in the general codebase.

[Ravish(OpenERP) (rmu-openerp)]

Hello Brett,

I have checked your issue.But I am not able to reproduce it.

Please provide Specific and Sufficient information about this issue.

Waiting for your reply.

Thanks

[Ian Beardslee (ibeardslee)]

I can confirm it.

Ubuntu Precise server running the OpenERP server.
Ubuntu Lucid running the Postgres server

Create a new database through the OpenERP interface, set to English UK and load the demo data.
Set the Administrator user Timezone to Pacific/Auckland (GMT+12)
Install the account_accountant module (Accounting and Finance)
Accounting >> Reporting >> Accounting Reports >> Legal Reports >> Trial Balance.

The date and time on the servers are ..
ian@dolven-openerp:~$ date
Thu Apr 19 08:36:00 NZST 2012

ian@dolven-db:~$ date
Thu Apr 19 08:35:56 NZST 2012

The time at the top of the Trial Balance report (attached) is 18/04/2012 20:36 which is last night, not this morning when I ran it.

[Ravish(OpenERP) (rmu-openerp)]

Hello Brett,

It's is server issue that why ,I am making it invalid for addons.

If you apply this following code then you can get date&time as per your time zone

[[formatLang(datetime.now(pytz.timezone(user.context_tz)).strftime('%Y-%m-%d %Z%z'), date=True)]][[ datetime.now(pytz.timezone(user.context_tz)).strftime("%H:%M")]]

Thanks!!

[Brett Wilkins (brettwilkins)]

Hi Ravish,

I am completely aware that this is a server issue - openobject-server, that is. My colleague Ian added addons as it effects on addons as well (addons could use the new functionality, etc).

I'm also completely aware that I can use the above to get date&time in my time zone. in fact, that's almost the exact code that I have to do so (except there's no real point to using formatLang IMHO).
However, pytz is not available in the context of the eval'd statements in a stock install of openerp, rendering the above useless.

What I created this bug for was to generate discussion around date, time and timezone related functions, and hopefully get some sort of functionality into core that would be useful in the codebase as well as in reports like this.

Would it be preferable if I came up with a patch/merge that deals with this problem?

[Amit Parik (amit-parik)]

Hello Brett,

I appreciate your passions on this issue.

It is very good for us, If you will provide a patch for it or better to create a branch for it and put merge proposal.

Thank you!

[Brett Wilkins (brettwilkins)]

Hi Amit and Ravish,

My apologies, I thought Ravish had set the status of the whole bug to invalid, rather than just for addons. I am still acclimatising to launchpad.

Thanks,
Brett

[wowas (wowas18)]

I think that more safely get timezone shift from Web client than from python on server side. Because browser, as a rule, have a real local time. And users can forget to set the time zone in OpenERP preference, as well as once again there are problems with daylight saving time.

[Ian Beardslee (ibeardslee)]

Absolutely no way should OpenERP be getting timezone from the webclient, that relies on people having the computer they are using to have it's clock set correctly.

If the database does all recording and returning of times in UTC, the server should be able to return that UTC date/time converted into the user's timezone.

The timezone being returned should come from ...
1) the user's own preferences
2) the default user preferences, failing that ..
3) the server's timezone.

[wowas (wowas18)]

But now, when working with Web client, OpenERP take time zone from browser, not from server, not from preference.
Then convert to UTC and send to server. There is no information to restore the local time.

If we relies on preference then need that web client also relies on it. Because we save time with shift from TZ browser and then try restore local time with shift from TZ preference.

Example:
We create data records with fild DATETIME in browser with TZ shift +4hours from UTC:
How look records for user in browser (TREE VIEW)
1. 2112-05-01 01:00:00
2. 2112-05-01 05:00:00
How look records in DB
1. 2112-04-30 21:00:00
2. 2112-05-01 01:00:00
User try make report, and wants select all records for month - MAY 2112. Browser send to server, that need select all records with YEAR = 2112 and MONTH = 05. That make it correct, server must convert date from records in DB to TZ of user. But if TZ in preference (+2hours) not equal TZ in browser it receive wrong report:
Report for browser user
1. 2112-05-01 03:00:00
But records looks for user in browser (TREE VIEW)
1. 2112-05-01 01:00:00
2. 2112-05-01 05:00:00
User expect that records in TREE VIEW and in report must be the same!

In addition, the more likely the fact that the browser has a real time than the time zone in preference. Because user work with OS every day and he view system time every day. But when the last time he change preference OpenERP?

[Olivier Dony (Odoo) (odo-openerp)]

The OpenERP framework does have methods to compute/output datetime values in the timezone of the user.
You should never need to use pytz manually to accomplish this, that's one of the reasons the current report engine does not provide pytz in the report context - you don't need it.

API-wise, as of OpenERP 6.1 the fields.date and fields.datetime classes have relevant static methods: http://bazaar.launchpad.net/~openerp/openobject-server/6.1/view/4334/openerp/osv/fields.py#L261
 - fields.date.today() returns the current date in default value format for a ``date`` field (meant for _defaults)
 - fields.date.context_today(model, timestamp) returns the current date or specified timestamp as seen in the contextual timezone, in a format fit for date fields (meant only for special use, e.g. reports)
 - fields.datetime.now() returns the current datetime in default value format for a ``datetime`` field (meant for _defaults)
 - fields.datetime.context_timestamp(timestamp) returns the given timestamp converted to the contextual timezone (meant only for special use, e.g. reports)

However you should never need to call context_timestamp directly in report, as formatLang() will do it for you, based on the current user's timezone. I'm not sure why you call it superfluous or brittle.
formatLang() does what is necessary 99% of the time: take a datetime value from a database record, and return a string that can be printed in a report for a specific user, in the appropriate format. Customizing the output format is not necessary in most cases, the date/time format is specified on the language itself, so using it everywhere is better for consistency. Using another timezone than that of the report context is also very uncommon.

So we mainly need to cover the case where you want to output the report printing date/time in the current user's timezone - that could indeed be done with extra now() and today() methods in the report context, doing what formatLang() does but with the current date/time.
We can of course add pytz and datetime to the report context, but using them should really be an exception.

Feel free to submit a merge proposal for this :-)

BTW, if you notice cases where reports are outputting incorrect datetime values, those should be separate bugs (missing formatLang calls), and are easily corrected.

Thanks!

[开阖软件 Jeff Wang (jeff.osbzr.com)]

I see this issue in the partner ledger report.

we use GMT+8 in china, but the top left of the report show UTC time, 8 hours difference.

this report is using internal report header with this line:

<drawString x="1.0cm" y="28.3cm"> [[ formatLang(time.strftime("%Y-%m-%d"), date=True) ]] [[ time.strftime("%H:%M") ]]</drawString>

I am not sure I fully understand what Oliver posted at last, but the issue still there, I don't know it is a bug in python(formatLang) or xml(the internal header)