[6.1] TimeZone ERRORs in statistical reports

[wowas]

In All statistical reports, that use extract Date, Month and etc. from timestamp, there are an error on the border of days.

For example in "crm\report\crm_lead_report.py":

                    to_char(c.create_date, 'YYYY') as creation_year,
                    to_char(c.create_date, 'MM') as creation_month,
                    to_char(c.create_date, 'YYYY-MM-DD') as creation_day,

don't worry about timezone and convert to year, month and day in UTC time.
But if we have stored date 2012-05-31 23:00:00 and our time zone +4hours we get ERRORs from such convertions.
Local time must be 2012-06-01 03:00:00 and expected day - 1 JUNE, but we get day (for this record) - 31 MAY.

[wowas (wowas18)]

Need redesign all addons that use trick, because it ERROR in certified addons!

[Amit Bhavsar (Open ERP) (amb-openerp)]

Hello wowas,

I have tested your scenario, and I have faced the same problem. so it's should be change in *_report.py file for all statistical reports. That's why as generic way I am assign this Issue to addons1 team with medium Importance.

@team: I request to addons1 team, would you please check the all statistical report which have use create_date.

Thanks

[SnippetBucket.com (tta-openerp)]

Hello wowas,

"First of all does not require to change any reports code for timezone."

Server will manage all date with `UTC` timezone and it supply to web client.
And Web client will receive datetime in `UTC` format and it have user's timezone.

Web client should have to perform this datetime with user's specified timezone.

Thanks,
Tejas - TTA

[wowas (wowas18)]

Hello Tejas Tank,

I don't agree with you!

Because in statistical report code transform datetime to day, month and year. And don't worry about timezone.
This transformation lose absolute time! Because this day, month and year from UTC point view!
Web client don't able correct restore day, month and year from UTC day, month and year, because time information lost.
Error apear on border of day!

[wowas (wowas18)]

As for example from first post. User input some data 2012-06-01 in local timezone (time about border of day, depend from timezone). Save to server. But when he will read the same data, it will from 2012-05-31 in his local timezone.

[wowas (wowas18)]

If we wont group and filter data on server side, then for us need timezone information! Otherwise on client side need send all database table and then extract needed rows (for big table it will be very long!).

[wowas (wowas18)]

I make report as following:

web client side
1) Add code to webclient, that it send through context timezone shift from UTC (from browser view).

server side
1) Get timezone shift from context
2) Read table
3) Correct datetime with timezone shift and save it to new temporary column (datetime in local timezone for a particular user)
4) Group and filter on corrected datatime (in this point code the same, that without timezone)