USM Dashboard Context Selector time is offset by an hour

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

This a clarification about the use of the context selector widget in the Oerator Console (OC) Dashboard Designer webapp.

The context selector is used to set a time range for an associated widget to interactively filter by a preset list of time ranges (Last hour, Last 4 hours, etc). In a recent use case, we saw that the "last hour" setting on the context selector showed data being one hour off depending on the local time setting.

Environment

• Any OC version

Resolution

When the user has a context selector on a dashboard, they are choosing to do substitution in the SQL query using ${startTime} and ${endTime} parameters in the queries they write. When that gets executed on the backend, we get the epoch time in milliseconds to substitute in the query.

When that gets added into the query, you see something like the following: created > dateadd (SECOND, 1498515655296/1000, '1970-1-1')

The problem is that the “created” date is aware of daylight savings, but the date created from the dateadd method is not. The result of the dateadd will give a datetime in UTC. When we compare against the “created” date, it is offset by an hour because of daylight savings.

That is why we are getting data an hour old because the millis are creating a date that is UTC, which is an hour ago, because this system is in daylight savings. 

If you are using SQL Server 2016, you could look at using “at time zone” to select the “created” column as a UTC date. Otherwise, you could try something like this: Dateadd(hour, DATEDIFF(HH, GETUTCDATE(), GETDATE()), dateadd (SECOND, 1498515655296/1000, '1970-1-1'))

Additional Information

For more details on how to use a context selector please review this tech doc:

https://knowledge.broadcom.com/external/article?articleId=9870