We have created a widget for our Support teams to see maintenance schedules.
I am trying to get it to display all times in EST (-5GMT) but most are showing GMT time.
I have attempted to use TOLOCAL but I am trying to find a clean way to get this working.
Any tips to get this to show in EST time only?
SELECT [N].DisplayName
, [N].Caption
, [N].DetailsUrl
, [N].Status As NodeStatus
, [N].StatusIcon
, [M].SuppressFrom As Mute_Start
, [M].SuppressUntil As Mute_End
, [V].GuestState
, TOLOCAL(Mute_Start) AS [Est_Time_Start]
, TOLOCAL(Mute_End) AS [Est_Time_End]
,
CASE
WHEN ([N].UnManaged = 'TRUE' AND [M].SuppressFrom IS Not Null)
THEN 'Muted,Unmanaged' WHEN ([N].UnManaged = 'TRUE' AND [M].SuppressFrom IS Null)
THEN 'Unmanaged' WHEN ([N].UnManaged = 'FALSE' AND [M].SuppressFrom IS Not Null)
THEN 'Muted' ELSE 'UNKNOWN' END AS Status , CASE WHEN [N].UnManaged = 'TRUE'
THEN (N.UnManageFrom) ELSE NULL END As Unmanage_Start , CASE WHEN [N].UnManaged = 'TRUE'
THEN (N.UnManageUntil) ELSE Null END As Unmanage_End FROM Orion.Nodes [N] LEFT OUTER
JOIN Orion.VIM.VirtualMachines [V] on [N].NodeID = [V].NodeID LEFT OUTER JOIN Orion.AlertSuppression [M] on [N].Uri = [M].EntityUri WHERE ([M].SuppressUntil >= TOLOCAL(DATETRUNC('day', ADDDAY(-1, (GETDATE()))))) ORDER BY Mute_Start
