openerp-web always uses the same (wrong) time zone

openerp-web does not exactly "use a timezone", it fetches UTC data from the server and shoves these in javascript Date objects for display. These objects do have a concept of "Local Time Zone", which they get from their "host environment" (the web browser and by extension the OS). This is both under the assumption that the host is correctly set up and because most third-party widgets need and use Date objects, both consuming and producing, in its local form.

openerp-web makes absolutely no use of time zones configured in openerp at this point.

What kind of setup do you have that you'd get incorrect time zones in openerp-web (but I assume not in your system clock)?

Hello, a possible reason you see this problem. We had teh same issue here after a migration. By default your server get your local timezone and postgres uses it.
Well that won't work with OpenERP 6.1!
Cause else, for instance when you create a record, it will use postgres now() function that will be your local time. But as OpenERP believe it's UTC when reading from the database, it will blows it when reading it back.

Apparently you need to set your posgres server timezone to utc (conf file) and then OpenERP will translate to and from utc properly according to the user timezone. At least for us it fixed our issue. Hope this helps.

BTW, as the timezone thing is a session param, wouldn't it be better that every OpenERP Postgres session registers itself as UTC without people having to hack their Postgres installation?

Hi,

I don't think this is a bug, and to get matters straight, here is a description of how timezone support is designed to work in OpenERP 6.1 (quite different from 6.0 and earlier):

1. Framework/Addons python code
OpenERP 6.1 now uses exclusively UTC timestamps for server-side computations and storage. At startup the server forces the Python timezone to be UTC, so that any call to datetime-related modules will always use UTC by default. As Xavier said the server exchanges UTC timestamps with clients, and the timezone-specific rendering is only ever done on the client-side.
The only case where the Timezone setting of the user preferences matters is for reports, because the rendering is performed server-side, so the user timezone needs to be applied. This is supported by formatLang(), a function available inside reports for rendering date time values.

2. Timezone-specific rendering in clients
* GTK client will render the datetime values according to the "Timezone" configured in the user preferences. If no timezone is selected, it will display datetime values in the server-side timezone, i.e. UTC.
* Due to Javascript's limited native timezone capabilities, the 6.1 web client cannot apply the "Timezone" that is set in the user preferences, and thus always renders value in the local timezone of the browser (which should usually be a sensible choice). The user timezone setting still matters for reports, as explained in 1.

3. Database storage
Datetime values are also exclusively stored as "timestamp without timezone" in the database, so Postgres will never alter the values, and they will be handled as UTC all the time. We also don't use "select now()" at DB level for initializing new record values, that would require an unnecessary db query - datetime.datetime.now() or time.strftime() work well enough for us.
In the few cases where we need to use now() (e.g. for WHERE clauses comparing with the current time) we now use "now() at time zone 'UTC'". The timezone of the DB connection is therefore irrelevant by design.

These 3 rules seem to explain the behavior described in the bug description: the datetime values are stored as UTC and displayed in the web client using the local browser timezone, which is apparently not UTC - this is the expected behavior.
You will usually get the expected behavior in all OpenERP clients if you properly set your local browser timezone (OS user timezone by default), and set the same timezone in the User preferences (for reports and GTK).

This is the intended design, there might of course be some left-over cases where these rules are broken, but there should not be that many (e.g. missing `formatLang` calls in reports, etc.). These would be bugs and we are fixing them as we find them

We'll also add a tooltip on the Timezone user preference field to explain this, and include the above explanation in the release notes and developer documentation.

Please reopen this bug (or a new one) if you notice a case that does not seem to match the above rules.

Thanks,

Hello Olivier,

what about that need to set Postgres server as UTC? Before setting it UTC,
it's not working properly here in Brazil (with the clients having the
proper America/Sao Paulo timezone).
I had to set it UTC and this is not the default configuration because by
default Postgres takes the timezone of the OS (which is not UTC here).
So was that expected by you? Wouldn't be better to force the UTC timezone
in the Python connection to Postgres?

Regards.

On Thu, Jan 26, 2012 at 8:11 AM, Olivier Dony (OpenERP) <
<email address hidden>> wrote:

> Hi,
>
> I don't think this is a bug, and to get matters straight, here is a
> description of how timezone support is designed to work in OpenERP 6.1
> (quite different from 6.0 and earlier):
>
> 1. Framework/Addons python code
> OpenERP 6.1 now uses exclusively UTC timestamps for server-side
> computations and storage. At startup the server forces the Python timezone
> to be UTC, so that any call to datetime-related modules will always use UTC
> by default. As Xavier said the server exchanges UTC timestamps with
> clients, and the timezone-specific rendering is only ever done on the
> client-side.
> The only case where the Timezone setting of the user preferences matters
> is for reports, because the rendering is performed server-side, so the user
> timezone needs to be applied. This is supported by formatLang(), a function
> available inside reports for rendering date time values.
>
> 2. Timezone-specific rendering in clients
> * GTK client will render the datetime values according to the "Timezone"
> configured in the user preferences. If no timezone is selected, it will
> display datetime values in the server-side timezone, i.e. UTC.
> * Due to Javascript's limited native timezone capabilities, the 6.1 web
> client cannot apply the "Timezone" that is set in the user preferences, and
> thus always renders value in the local timezone of the browser (which
> should usually be a sensible choice). The user timezone setting still
> matters for reports, as explained in 1.
>
> 3. Database storage
> Datetime values are also exclusively stored as "timestamp without
> timezone" in the database, so Postgres will never alter the values, and
> they will be handled as UTC all the time. We also don't use "select now()"
> at DB level for initializing new record values, that would require an
> unnecessary db query - datetime.datetime.now() or time.strftime() work well
> enough for us.
> In the few cases where we need to use now() (e.g. for WHERE clauses
> comparing with the current time) we now use "now() at time zone 'UTC'". The
> timezone of the DB connection is therefore irrelevant by design.
>
> These 3 rules seem to explain the behavior described in the bug
> description: the datetime values are stored as UTC and displayed in the web
> client using the local browser timezone, which is apparently not UTC - this
> is the expected behavior.
> You will usually get the expected behavior in all OpenERP clients if you
> properly set your local browser timezone (OS user timezone by default), and
> set the same timezone in the User preferences (for reports and GTK).
>
> This is the intended design, there might of...

On 01/26/2012 01:11 PM, Raphaël Valyi - http://www.akretion.com wrote:
> I had to set it UTC and this is not the default configuration because by
> default Postgres takes the timezone of the OS (which is not UTC here).
> So was that expected by you? Wouldn't be better to force the UTC timezone
> in the Python connection to Postgres?

Quoting my previous comment:
"the timezone of the DB connection is therefore *irrelevant by design*."

So I would say no, what you describe is not expected. And no, it would
not be better to force UTC on DB connections. Changing the DB timezone
is not supposed to modify the 6.1 behavior, so if it did for you, that
means there's a possible bug somewhere. Forcing the database connection
timezone in the hope that it will solve a bug would not be a good
idea... in fact, not forcing it could even help spot potential bugs, or
so it seems.
You're assuming we use the database's "now()" to initialize new record
values, but that's not the case. Please grep the codebase if you don't
trust me on this.

If you have the time, I think it's worth analyzing further, so that we
know whether something obvious is missing in the 6.1 design or not.
Please don't assume the system works in one way or another, rather read
my description and get the full facts such as: based on standard OpenERP
addons, what datetime values were actually stored in the database (type
and value), what was read, written, transmitted, displayed, and with
which timezone settings in the server and client OS, user prefs, etc.
Without this information it's very hard to tell what is wrong in your case.

Thanks for your feedback!

Hello Olivier,

well I swear that yesterday (not yesterday today as in OpenERP America
timezone, I really mean yesterday lol), my customer would see the date of
all create_date update_date late by 2 hours in Brazil (we are UTC + 2),
while after I set the Postgres conf to UTC (instead of UTC + 2 by default)
new records would get their create_date and write_date properly set.
The the experimentation kind of contradict the theory here.

Also, take a look to osv/orm.py and you will see that you do use the now()
postgres function!

What was happening is: dates entered in the user interface where correct:
they where translated to UTC by the server, stored properly in the database
and translated properly back to local time when read on the client.
Now, for dates like create_dates (they are used a lot), those dates seems
to be generated by the the postgres now function, so they where the value
of the local time, but in what OpenERP would believe would be UTC (the
database), so when reading the date on the client, OpenERP would shift by 2
hours and show a wrong time.

Like, at 17h24:
a date entered by the user on the client would be properly translated to
19h24 in the database by OpenERP
now, the crate_date would be created using now() as 17h24 in the database.

So on the client, the user entered date is 17h24 as expected, but the
create_date appears as 19h24 if the database is not forced as UTC.

CQFD

Hope this helps. Thanks for your work.

On Thu, Jan 26, 2012 at 12:18 PM, Olivier Dony (OpenERP) <
<email address hidden>> wrote:

> On 01/26/2012 01:11 PM, Raphaël Valyi - http://www.akretion.com wrote:
> > I had to set it UTC and this is not the default configuration because by
> > default Postgres takes the timezone of the OS (which is not UTC here).
> > So was that expected by you? Wouldn't be better to force the UTC timezone
> > in the Python connection to Postgres?
>
> Quoting my previous comment:
> "the timezone of the DB connection is therefore *irrelevant by design*."
>
> So I would say no, what you describe is not expected. And no, it would
> not be better to force UTC on DB connections. Changing the DB timezone
> is not supposed to modify the 6.1 behavior, so if it did for you, that
> means there's a possible bug somewhere. Forcing the database connection
> timezone in the hope that it will solve a bug would not be a good
> idea... in fact, not forcing it could even help spot potential bugs, or
> so it seems.
> You're assuming we use the database's "now()" to initialize new record
> values, but that's not the case. Please grep the codebase if you don't
> trust me on this.
>
> If you have the time, I think it's worth analyzing further, so that we
> know whether something obvious is missing in the 6.1 design or not.
> Please don't assume the system works in one way or another, rather read
> my description and get the full facts such as: based on standard OpenERP
> addons, what datetime values were actually stored in the database (type
> and value), what was read, written, transmitted, displayed, and with
> which timezone settings in the server and client OS, user prefs, etc.
> Without this information it's very hard to tell what is wrong i...

On 01/26/2012 04:34 PM, Raphaël Valyi - http://www.akretion.com wrote:
> a date entered by the user on the client would be properly translated to
> 19h24 in the database by OpenERP
> now, the crate_date would be created using now() as 17h24 in the database.
>
> So on the client, the user entered date is 17h24 as expected, but the
> create_date appears as 19h24 if the database is not forced as UTC.

Alright, so the only dates that were incorrectly set were the
{create,write}_date fields that are automatically set by the ORM. I was
worried about actual date values computed by addons code (e.g. default
values) or entered by the user, which would indicate an error in the design.
The {create,write}_dates are a known issue, some of the few remaining
places were we need to fix the use of now(). Thanks for the additional
feedback.

Well I am seeing errors in addons code. GTK client in UTC+13 timezone. Whenever dates are calculated for anything it depends whether it is morning or afternoon which day I get. e.g. Set total leadtimes up at 1 day and raise a PO, in the morning I get today, come 11am I get tomorrow.

On 02/01/2012 06:16 PM, Graeme Gellatly wrote:
> Well I am seeing errors in addons code. GTK client in UTC+13 timezone.
> Whenever dates are calculated for anything it depends whether it is
> morning or afternoon which day I get. e.g. Set total leadtimes up at 1
> day and raise a PO, in the morning I get today, come 11am I get
> tomorrow.

Graeme, do you observe this behavior only for 'date' fields, and never
for 'datetime' fields? If yes, this issue always existed and is a
consequence of the fact that pure date values cannot be converted to
other timezones, ever.
The problem has only become more frequent and apparent in 6.1 because
the timezone used by addons/framework is now UTC, and thus a lot farther
from the user's timezone than for 6.0 (where the local timezone of the
server machine was used)

This is really a different issue so I have just reported bug 925361 with
a more detailed explanation and some possible solutions.
Any feedback on this topic would be very much appreciated!

Thanks!

Thank you Olivier for your explanations.
About reports rendering, I found a problem described in bug 932170

Hello,

As I had originally reported, there is one more place which suffers from datetime conversions: the log. How important that is depends largely on the importance of the log (e.g. an audit could take it more seriously than a developer).

Now I have proper (local time) for the OS, therefore javascript, same for GTK, hours are shown correctly. however the log is one hour early right now.

hope I an not too off-sbject.
regards,
Bogdan

I don't know if this is the same bug, but I found that in the 6.1 version the GTK client is DST (daylight saving) aware and the web client is not.

How to reproduce this behaviour:

1) Configure the user timezone to "America/Sao Paulo" (UTC-3).
2) Open the GTK client and add a "task work" to an existing task, setting the date/time to "2011-10-10 12:00:00".
3) Open the database and see how it is correctly recorded as "2011-10-10 14:00:00". This is right, since this date is in the DST period.
4) Open the task in the web client. It is displayed as "2011-10-10 11:00:00". DST is not considered when translating back to local time in the Web client.

Luciano Schirmer

One last observation: setting "timezone = 'UTC'" in the postgresql.conf and restarting postgresql didn't help.
The weird behaviour with the DST difference persists.

Another consideration:
GTK client considered DST from 2011-10-09 to 2012-02-18.
The correct is 2011-10-16 to 2012-10-26 here in Brazil.

Hi,

(my related issue here https://bugs.launchpad.net/openobject-server/+bug/1012501)
Based on comment #4 by Olivier, here are 2 issues (not related to create/write date):

1. My local timezone is GMT+7 (Vietnam).
- I set local time for host environment (which openerp-server runs in) is 03:00:00 (AM).
- openerp-server results in "2012-06-12 20:00:00" (posting date field). Its behavior is as design specs. (posting date is written automatically with strftime() when pressing 'Post' button)
- But when in search view, I do search posting date with value of today (2012-06-13). Search results do not contain this record. But value of (2012-06-12), it includes this record.

2. the second related to SQL queries. When executing cr.execute() with some conditions on date field (posting date as above), it does wrong, as above. Besides, if I use SQL queries outside openerp-server environment. It's really serious. What should I do in this case? I think by some means, date stored in DB must be in localtime zone.

Thanks,

Hi Guys!
Well I have the same problems. I have been doing some further tests. Not what is written in the database, but how the different modules handle time/time zone. These test are done using 3 different server environments installed with v6.1 and using the web client; 1: server2003, 2: win7 and 3: the online paying service my.openerp.com. I have tested 3 modules; sales, warehouse and human resource. I have also tested the modules on the actual server and on another computer on the LAN via the web interface.

1. Is is quite obvious that it is not that easy to get correct.
2. what is written in config-files for db-server, erp-server or web client have no effect on what is displayed as been mentioned before.
3. The modules get the time from the computer, but how each module calculates the final time that displays is different . For me it is a constant time difference from displayed time and computer time of 3h. Except for sales which always display the correct time.

After a number of test with change of time zone and change of clock I have come to the following conclusion that the Sales module always display the correct time, both on the server and on the LAN-computer no matter what I change. The online service displays almost the correct time compared to local time. If I change time zone it follows, but If I change only the clock it does not follow, except for the sales module.

Unfortunately, I do not have the competence yet to look into the programming of these modules, but I would say that the person that have written the Sales module have made a very good job!

-Henrik

I forgot: obviously the guys ho runs the on-line service knows a lot about this so a little input from them would of cause be very useful ? :).
-Henrik

I'm using times in a comment field. Do I have to get the clients timezone and convert the time?

I think this timezone problem is a BUG!

If openerp is the best one than it must have configurable timezone settings in server side and able to change timezone depending when ( which country) openerp server located.

If i using openerp server in Asia then server side must be use Asian timezone. Not UTC.

On 09/12/2012 04:28 AM, Jacara wrote:
> If i using openerp server in Asia then server side must be use Asian
> timezone. Not UTC.
>

Why?
Following the concepts explained here
https://bugs.launchpad.net/openerp-web/+bug/918257/comments/4 everything
works.

The search view has still got the bug with respect not honoring the timezone while searching.
Has Reported the same at https://bugs.launchpad.net/openobject-client/+bug/1093074

nevertheless there is a big problem querying datetime especially from different time zones

please see
https://bugs.launchpad.net/openobject-addons/+bug/1157014