07-13-2012 12:27 AM
Time zone difference between SM & DB records time stamp. we are using IST time zone for application.
Our application records time stamp & DB records time stamp for same field is 5 hr 30 min difference is there.
Please suggest how we can rectify this...without any impact on older records time stamp from both DB & Application end.
Thanks & Regards
07-13-2012 01:31 AM
Add the "sqltz" parameter in the sm.ini file and set it to the timezone of your HPSM. This will keep the HPSM and the DB in sync with respect to the timezone. Old tickets will automatically show the corect time in the DB.
07-13-2012 01:49 AM
I have set the time zone parametre in sm.ini after that DB & Application records time is in sync but all records having a difference 5hrs30min in application end also from original time stamp.
How much time it will take to show correct time.
07-13-2012 04:37 AM
I have tried as you suggested but both DB & Appl end are showing different time &Application end showing around 5hrs 30 min before.
Plesae suggest .............
07-16-2012 08:00 PM - edited 07-16-2012 08:03 PM
By default, Service Manager stores all date/time values in GMT format. This default can be changed by using the sqltz parameter in the sm.ini file. This parameter requires specifying a time-zone that is stored within Service Manager's tzfile. It also requires restarting Service Manager to take effect.
However, specifying the sqltz parameter WILL affect existing date/time values. It would be more accurate to state that it does not update the older date/time values which causes them to be wrong as compared to new values going forward.
This means there are three options to consider:
1. Don't implement the sqltz parameter and simply update reports by adding 5 hours and 30 minutes to date/time fields
2. Implement the sqltz parameter and only focus on new records going forward
3. Ask the DBA to mass update every date/time field in every SM table (excluding duration fields where year=40xx) and then implement the sqltz parameter
Note: This isn't really practical or possible since not all date/time values are directly accessible from the RDBMS
If you decide to use the sqltz parameter, the following must be implemented
1. Add a tzfile record for the IST time-zone and name it India (any unique name is fine)
2. Add the parameter to the sm.ini file:
3. Restart Service Manager
Whether or not the sqltz parameter is implemented, users can still have a time-zone specified in their operator record. It is also appropriate to specify a default value within the System Information Record. The time-zone in the System Information Record/operator record only affects the display of the date/time in an SM client.
08-01-2012 08:04 AM
Can you please elaborate more on your option no 3 where we can do a backend update?
I want to know the feasibility of this solution. And do we need to update each and every table in SM which has a date/time field?
I have checked that putting the sqltz param in the sm.ini file at a later stage does not work for old tickets and works only for new tickets. So what's the best approach to fix this issue.
08-03-2012 12:37 AM
Anyone, any info on this issue?
I want to know that if the sqltz param was put at a later stage and not in the beginning when the HPSM was setup, then as a side-effect of it this param will take care of only new tickets and not the old tickets. What's the best way if customer wants to retain this param and also asks to update all the old tickets? Do we need to update only the tickets or each and every SM table which has a date\time field? Is there any other impact of having this approach?
08-03-2012 01:13 AM
Yes. its a big mess but if you have enough time can be managed.
First your us.notify wont work properly it will start throwing error related to time zone. If you have applied proper patch this can be avoided.
Do you have any SLO which are dependent on work schedules? you need to reconfigure all these again.
Update your contacts with respective time zone.
Right now i am visualising only these major issues.
08-03-2012 02:09 AM
Thanks for the help. But I think there is some confusion here. We are not changing any timezone in the SM. We just want the timezone to be same in both SM and DB by adding sqltz param in the sm.ini file. But as I said, this works perfectly fine for new tickets that are created after we put this param and not for old tickets.
So we only have to update all the date/time fields in all the old tickets.
And we don;t have any SLOs. We checked that the notification is also working fine for all old tickets.
So it seems that we just need to update the date/time fields in all the old tickets and deduct 4 hrs to make them in sync with the DB data.
Current TZ in SM: US/Eastern
08-03-2012 05:35 AM
then it should work logically.
Be on safer sid, first take backup of all tickets if possible(considering limited number of tickets). or try to update date/time field of few selected tickets , perform activity on these tickets (like update or close). check mails and SLA.
08-06-2012 01:56 AM
If the system is live and you apply the sqltz command, you might get errors stating that Approved date is not able to convert string to array.
To avoid the same, in the tzfile that you choose, ensure the DB and SM date/time format is same or write the following command in sm.ini.
Note:- date/time format as set in DB.