On Thu, Aug 26, 2010 at 6:35 AM, Hugh Kelley <
hugh.kelley@gmail.com> wrote:
> 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 <
hugh.kelley@gmail.com> 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 <
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
>>
>
>