Time zone conversion on Orion database using an SQL query

11/21/2018 9:20 PM

10/7/2022 4:05 PM

Some Orion database tables use the same timestamp as the Main polling engine, but others do not. This article includes a query that customers can use to convert and display the time for a different time zone. 

Note: This does not change the timestamp on the table, rather, it would just show a new column with the converted time.

Orion Platform

n/a

This can be used either on the database manager or SQL Management Studio.

Note : We highly recommend to perform database back before make changes. 

Depending on which table you are looking at, you might have to change the query depending on the name of the column. See example below:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

Select [AlertHistoryID], [AlertID], [Name], [ObjectType], [TimeStamp]
FROM [dbo].[AlertHistoryView]

 

To recreate this result, use the query above on the database manager.

Note: The timestamp on the image below is for the local time of my Orion server which is Manila time.

 

If you would like to have a column where it would convert the time to a different timezone, use the query below:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT [AlertHistoryID], [AlertID], [Name], [ObjectType], DATEADD(hour, -8, [TimeStamp]) as [TimeStamp]
FROM [dbo].[AlertHistoryView]

 

The query above needs to set the local time(Manila time) on the database as UTC time which is -8 and the result would be this. You can convert the time to any time zone using the query above as long as you adjust the hour added as you see fit.

 

 

Definition and Usage 

The DATEADD() function adds or subtracts a specified time interval from a date.

Syntax 

DATEADD(datepart,number,date)

Where date is a valid date expression and number is the number of interval you want to add. The number can either be positive, for dates in the future, or negative, for dates in the past.

datepart can be one of the following:

DATEPART	ABBREVIATION
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns