I've dug into this a bit further and can offer this:

First, the OTRS code itself is inserting time stamps in the database as local (to the database server) time.

From Ticket.pm:

        SQL => "INSERT INTO time_accounting "
            . " (ticket_id, article_id, time_unit, create_time, create_by, change_time, change_by) "
            . " VALUES (?, ?, $Param{TimeUnit}, current_timestamp, ?, current_timestamp, ?)",

For this to put proper UTC times in the database it would need to change from CURRENT_TIMESTAMP to:

SQL Server:    SYSUTCDATETIME
    MySQL:      UTC_TIMESTAMP

Since there is no true cross-platform function for UTC time, it would seem most safe to add a user-defined function to the OTRS DB like OTRS_SYS_TIME. In SQL Server, creating a UDF is a trivial matter. I think MySQL is a bit trickier as it requires a C-style implementation, which introduces a few more OS cross-compatibility issues.

Is this just a problem for Windows DB implementations? Does MySQL on a Linux distro behave differently? Is this not an issue for most OTRS users? I see UTC-based time referencing as critical for my implementation (support across 9 time zones, a need to do integration with/reporting from the database, etc.), but I may be in the minority.

Hugh

On Tue, Aug 24, 2010 at 4:49 PM, Hugh Kelley <hugh.kelley@gmail.com> wrote:

Our staff work in multiple time zones so I need to store all ticket data in UTC.

I believe I have identified the options I want:

- On the server, include $ENV{TZ}='UTC'; in config.pm

- In the SysConfig, Core::Time , TimeZoneUserBrowserAutoOffset is set to Yes

From what I can see, the $ENV{TZ}='UTC'; setting is not taking effect in my environment.

- Windows Server 2008
- ActiveState Perl

Has anyone seen this configuration work before? Is there a page in OTRS that shows the "system clock"?

Hugh