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)?
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:
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.
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.
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?
Time zone name as defined in Service Manager time zone record (tzfile)
Command line: sm -httpPort:13080 -sqldb:dbserver -sqltz:Canada/Atlantic
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.
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.