Re: [otrs] Windows Server Time Zone

One last discovery. If you are setting this environment variable on a Windows host (Windows 7 or earlier), expect strange behavior from JavaScript engines in browsers: - IE will ignore DST settings when calling getTimezoneOffset() - Firefox seems to just return zero when calling the same Either way, your user session in OTRS will have either the wrong (or no) UserTimeZone value. Fortunately, this is only likely to be an issue if you run the browser on the OTRS server (as in a development mode). Hugh On Mon, Sep 6, 2010 at 3:01 PM, Hugh Kelley wrote:

The issue turned out to be a bug with ActivePerl's PerlEx. It doesn't seem to apply the timezone setting if you change it within config.pm (or anywhere else, for that matter).

I had even started explicitly calling tzset() in Config.pm and still there was no effect on locatime or timelocal.

$ENV{'TZ'} = 'UTC'; POSIX::tzset();

Hugh

On Sat, Aug 28, 2010 at 11:33 AM, Hugh Kelley wrote:

...

More updates.

First, I now realize that the the TimeZoneUser and TimeZoneUserBrowserAutoOffset options must both be enabled. I had thought they were unlinked. From InterfaceAgent.pm:

$Self->{ConfigObject}->Get('TimeZoneUser') && $Self->{ConfigObject}->Get('TimeZoneUserBrowserAutoOffset') && $LayoutObject->{BrowserJavaScriptSupport}

Setting both, I now see calculations occurring in the UI. Unfortunately, they are occurring twice. For example:

From the ticket_history table in the DB (UTC time)

id ticket_id article_id create_time 685 57 166 2010-08-26 04:04:18.373

From the UI (indicates a -4 offset but calculates a -8):

Created: 08/25/2010 20:04:18 (-4)

I have tried this with and without the $ENV{'TZ'} = 'UTC'; option in config.pm. The UI is the same either way.

I assume this double counting is occurring because both the back end and the presentation layers are calculating. I need to stop the back end calculations and have the server side simply rely on the UTC timestamps from the database.

Is there an authoritative document I should be reading to understand OTRS' time treatment? I'm getting a bit confused here because I feel like my findings don't match this guidance, http://faq.otrs.org/otrs/public.pl?Action=PublicFAQ&ItemID=335.

Hugh

On Thu, Aug 26, 2010 at 6:08 PM, Hugh Kelley wrote:

...

I am using the forms-based auth and I do see the TimeOffset hidden variable being posted at login.

Action=Login&RequestedURL=Action%3DAgentDashboard&Lang=en&TimeOffset=120

My Core::Time is at the default, other than this:

delete $Self->{'TimeZoneUser'};

config.pm is setting the server to UTC (and I used a quick Perl script outside of OTRS to confirm that the ActiveState interpreter works with this command)

$ENV{TZ}='UTC';

And finally, the database show the following UTC for my test article's create_time:

2010-08-26 11:37:20.050

Yet the UI insists on showing it as EDT (-4) (which is the local time zone of the web server).

08/26/2010 07:37:19

I would be grateful for any diagnostic hints.

Hugh

On Thu, Aug 26, 2010 at 6:27 AM, Michiel Beijen

...

wrote:

...

Hi Hugh,

That's an in-depth analysis...

Could it be that you use Single Sign On? Because, in that case we don't get the timezone information from the browser. We have an open bug report for that:

http://bugs.otrs.org/show_bug.cgi?id=5429

-- Michiel Beijen Senior Consultant

OTRS BV Schipholweg 103 2316 XC Leiden The Netherlands

T: +31 71 8200 255 F: +31 71 8200 254 I: http://www.otrs.com

OTRS brings true mobility to the Help Desk: The OTRS iPhone App free download http://www.otrs.com/en/products/iphone-app/

On Thu, Aug 26, 2010 at 6:35 AM, Hugh Kelley 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 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 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

--------------------------------------------------------------------- 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