To keep local time in a special table to be able to compute local time on server
We are using a special task category named 'On Call' to trace on call (= on duty, = readiness) incidents during out of office hours.
We have technicians in Europe, USA, and Asia: they may input ticket tasks with this special 'On Call' task category.
At the end of each month there is a consolidation of these data, and technicians may get extra money for 'On Call' operation.
The consolidation is done by an extract of the times input (begin and end of task) in the planning of the 'On Call' task. This is done by an external HR system which reads the glpi_tickettasks table.
As from now on, the datetimes are UTC, then the read times are not expressed in the timezone of the technicians but in the timezone of the server.
I.e.: imagine a US guy working on a task for which he sets: 'begin' 3:00AM UTC-4 and 'end' 3:30AM UTC-4. For this task we should pay for extra hours, as this technician worked out of US office hours. But if we retrieve these datetimes with the server timezone, then we get: 'begin' 9:00AM and 'end' 9:30AM (as our server's timezone is 'Europe/Paris' and the external HR application doesn't manage timezones), then in this case the technician will not get payed for extra hours as this slot is within normal office hours.
To solve this problem, I've added a new table called glpi_plugin_timezones_tasks_localtimes which stores those tasks times in VARCHAR to prevent any timezone shifts during INSERT or SELECT. The times are stored in local times (the one the technicians see). At plugin installation this table has been filled with datetimes from already existing tasks before the tables conversions.
This has been done for all tables with tasks (glpi_tickettasks, glpi_problemtasks, and so on).
- To have a report which permits to view and export these data.
- to have a script which corrects datetimes for tasks input before plugin installation (former task times where input in local time but interpreted as UTC+2 at plugin installation, when glpi_tickettasks has been converted with TIMESTAMP). Note: that for this we need to have timezone set for the task technicians to be able to perform the correct timezone shift.