Re: Time zone difference between SM & DB records time stamp. (1234 Views)
Reply
Regular Advisor
Vikky
Posts: 184
Registered: ‎12-12-2010
Message 1 of 13 (1,392 Views)

Time zone difference between SM & DB records time stamp.

Hi Experts,

 

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

 Vikky.

 

 

Honored Contributor
Mayur K
Posts: 292
Registered: ‎05-13-2009
Message 2 of 13 (1,385 Views)

Re: Time zone difference between SM & DB records time stamp.

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.

Regular Advisor
Vikky
Posts: 184
Registered: ‎12-12-2010
Message 3 of 13 (1,381 Views)

Re: Time zone difference between SM & DB records time stamp.

Thanks Mayur.

 

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.

 

Plesae advise...

 

Many Thanks

Vikky

Honored Contributor
ramesh9
Posts: 1,080
Registered: ‎04-19-2011
Message 4 of 13 (1,377 Views)

Re: Time zone difference between SM & DB records time stamp.

If you define sqltz parameter in sm.ini please make sure in System Information record, you do not set any timezone information....

Regular Advisor
Vikky
Posts: 184
Registered: ‎12-12-2010
Message 5 of 13 (1,370 Views)

Re: Time zone difference between SM & DB records time stamp.

Thanks Ramesh.

 

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

 

Many Thanks

Regular Advisor
Vikky
Posts: 184
Registered: ‎12-12-2010
Message 6 of 13 (1,347 Views)

Re: Time zone difference between SM & DB records time stamp.

Please suggest............

Thanks in advance

Honored Contributor
-m-
Posts: 989
Registered: ‎02-22-2011
Message 7 of 13 (1,330 Views)

Re: Time zone difference between SM & DB records time stamp.

[ Edited ]

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:

sqltz:India

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.

Honored Contributor
Mayur K
Posts: 292
Registered: ‎05-13-2009
Message 8 of 13 (1,299 Views)

Re: Time zone difference between SM & DB records time stamp.

Hi,

 

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.

Honored Contributor
Mayur K
Posts: 292
Registered: ‎05-13-2009
Message 9 of 13 (1,273 Views)

Re: Time zone difference between SM & DB records time stamp.

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?

Honored Contributor
Abhijit K
Posts: 903
Registered: ‎10-14-2010
Message 10 of 13 (1,269 Views)

Re: Time zone difference between SM & DB records time stamp.

Hi,

 

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.

 

Good Luck.

 

Regards ,

Abhijit K

Honored Contributor
Mayur K
Posts: 292
Registered: ‎05-13-2009
Message 11 of 13 (1,263 Views)

Re: Time zone difference between SM & DB records time stamp.

Hi,
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
sqltz: US/Eastern
Honored Contributor
Abhijit K
Posts: 903
Registered: ‎10-14-2010
Message 12 of 13 (1,254 Views)

Re: Time zone difference between SM & DB records time stamp.

 

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.

 

Regards,

Abhijit K

 

Regular Advisor
nij_jac
Posts: 124
Registered: ‎06-30-2011
Message 13 of 13 (1,234 Views)

Re: Time zone difference between SM & DB records time stamp.

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.

 

sqltextdateformat:mm/dd/yy

 

Note:- date/time format as set in DB.

 

Regards,

Nij

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.