What are Settings for GMT offset? (2138 Views)
Reply
Regular Advisor
Greg_S
Posts: 100
Registered: ‎10-21-2011
Message 1 of 9 (2,138 Views)
Accepted Solution

What are Settings for GMT offset?

On SM 7.11.  We just ran an ad-hoc query against the database tables and the date is 6 hours off from what is shows in the application. Specifically, select * from INCIDENTSM1 where incident_id = 'SD90332'

If I go into the app and pull up that interaction id, under Historic activities, date/time is exactly 6 hours off. That would mean that it is being stored in the database as GMT, but displayed as local time.

What are the flags or settings that tell the app to show time in GMT -6 (Central time zone)?

Advisor
Tanya Hornung
Posts: 33
Registered: ‎08-27-2007
Message 2 of 9 (2,135 Views)

Re: What are Settings for GMT offset?

Hi Greg,

 

Are you using Crystal Reports to run your ad-hoc queries?

 

Our DB2 database stores date/time information as GMT time. In order for me to run any date calculations or display the correct dates in my reports, I have to set up specific formulas for each date field.

 

For example, if I add the Open Time on INCIDENTSM1, I get my date stored as 20/12/2011 16:52:40. The date on my computer clock is actually 20/12/2011 10:52:40. I have to offset the GMT by using this formula:

 

dateadd("h",-6,{INCIDENTSM1.OPEN_TIME})

 

Therefore, the field in the database is 20/12/2011 16:52:40 but my field in my report is 20/12/2011 10:52:40.

 

Please let me know if this is what is happening to you. If you are using the Service Manager ODBC driver, there is a different method you have to use to have your dates show correctly for your time zone.

 

Thanks!

 

 

Respected Contributor
exeptt
Posts: 145
Registered: ‎05-14-2010
Message 3 of 9 (2,130 Views)

Re: What are Settings for GMT offset?

Hello Greg , Tanya !
Please find in help for sm - sqltz parameter.
HTH.
Regular Advisor
Greg_S
Posts: 100
Registered: ‎10-21-2011
Message 4 of 9 (2,119 Views)

Re: What are Settings for GMT offset?

Database is Oracle, and we use Benthic or Toad to run quick SQL queries.
Regular Advisor
Greg_S
Posts: 100
Registered: ‎10-21-2011
Message 5 of 9 (2,119 Views)

Re: What are Settings for GMT offset?

did a grep in the RUN directory and found nothing referencing sqltz.
Respected Contributor
exeptt
Posts: 145
Registered: ‎05-14-2010
Message 6 of 9 (2,117 Views)

Re: What are Settings for GMT offset?

[ Edited ]

If you won set up sql time like sm, add in sm.ini - sqltz:

Possible values
Time zone name as defined in HP Service Manager time zone record (tzfile)

 

Like example

record from tzfile defines time for Berlin (Name Berlin , offset from GMT 2:00:00)

in sm.ini you need add sqltz:Berlin

restart sm server

now all time record in db - have +2 hour from GMT, not UTC

HTH.

HP Expert
Francis Feugue
Posts: 137
Registered: ‎06-21-2007
Message 7 of 9 (2,108 Views)

Re: What are Settings for GMT offset?

I can tell you something about the 6 hours difference: date/times are by default stored in GMT. When a user enters/opens a ticket inFranceit will get the open.time of the local timezone e.g. 1/1/2009 12:00:00, when it actually is stored in the RDBMS it is converted into GMT which is one hour back (1/1/2009 11:00:00) (You can check that by looking at the data at the backend). When a user inFrancequeries all the tickets opened at 1/1/2009 12:00:00, the query is modified the same way, it will look for 1/1/2009 11:00, and therefore find the ticket that was opened at 1/1/2009 12:00:00 local French and the one that was opened 1/1/2009 13:00 local Moscow’an time

 

If you don’t like to look at the date times in the backend as GMT there is a parameter –sqltz:Europe/Middle which should change that. However, it will NOT change already stored data on the backend.

Possible value are the record from the tzfile table in Service Manager

 

From Help server:

 

SQL parameters: sqltz

Startup parameters change the behavior of the @PRODUCT NAME@ server. You can always set a startup parameter from the server's OS command prompt.

Parameter
sqltz
Description

This is an optional parameter that the Service Manager server uses to control storing of date and time values in the RDBMS. This parameter defines the time zone to use as a base for all date and time values. The time zone is specified as the name of the time zone record in the Service Manager tzfile. The default time zone is Greenwich/Universal (GMT).

Valid if set from
server's OS command prompt
Initialization file (sm.ini)
Requires restart of Service Manager server?
Yes
Default value
Greenwich/Universal (GMT)
Possible values
Time zone name as defined in Service Manager time zone record (tzfile)
Example usage
Command line: sm -httpPort:13080 -sqldb:dbserver -sqltz:Canada/Atlantic
Initialization file: sqltz:Canada/Atlantic
Honored Contributor
-m-
Posts: 989
Registered: ‎02-22-2011
Message 8 of 9 (2,091 Views)

Re: What are Settings for GMT offset?

The time-zone specified in the System Information Record and the operator records control the offset used to display a date/time value in a Service Manager client. The time-zone information itself (names, Daylight Savings Time offsets, etc) is stored in the tzfile. As previously mentioned, GMT defaults as the time-zone used to store date/time values in the database.

 

If you decide to use the sqltz paramter to change the time-zone used for storage in the database, please note the significant warning in the update from Francis: However, it will NOT change already stored data on the backend.

 

This means that values in older records will be off by 6 hours when viewed/used in Service Manager.

Regular Advisor
Greg_S
Posts: 100
Registered: ‎10-21-2011
Message 9 of 9 (2,086 Views)

Re: What are Settings for GMT offset?

Thanks, m! Great precaution. For now, I do not intend to make any change other than to include an hour adjustment to my SQL queries. Dateadd, as Tanya suggested, does not work because we're on Oracle. I'm no SQL expert, so I'd welcome hints on how to change the hour only using Oracle SQL.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.