I spoke too soon. This was already anticipated by the developers.
If you are running on MS SQL Server, see Kernel/System/DB/mssql.pm, line 50:
# set current time stamp if different to "current_timestamp"
$Self->{'DB::CurrentTimestamp'} = 'SYSUTCDATETIME()';
This will perform a replacement of "CURRENT_TIMESTAMP", as desired in my
case.
Now I need to concentrate on the client-side detection. Neither Firefox nor
IE is sending any timezone related headers.
On Wed, Aug 25, 2010 at 8:13 PM, Hugh Kelley <[email protected]> wrote:
> 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 <[email protected]>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
>>
>
>
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
