Is there a way to automatically convert the timestamp of an alert from UTC to whatever the local time zone happens to be for the user?
Something like this might work:
${SQL: SELECT Convert(Date,DateAdd(mi,DATEDIFF(mi, GETUTCDATE(), GETDATE()),TimeStamp)) AS Day_Local ....(rest of query) }
In the alert, I think you're going to be locked into the (Orion) server location. This _might_ work for viewing through the web console. I've noticed in a few places that it does have timezone awareness.
Another approach, if you want the time in the alert, is if you are tracking location of the device that the user is responsible for. If you have the timezone as a custom property, you can off-set based on the property.
SELECT DateAdd(Hour, Nodes.TimeZone_Offset, dbo.table.DateTime)
Thanks for the quick response lag. Lee me see if I understand.
The first query would work to display the user's local time for any alert as long as it is inserted in the alert message? The second query would reference the timezone custom property and then convert the UTC stamp?
The first query would let you convert UTC to local timezone, which would be local to Orion (the conversion is happening at Orion before sending out the message as an alert trigger action). I _believe_ if you are building a report using the conversion, then the date is converted to web user local. <- I 'think' this is true in the web, but you should verify. I've seen places where time is converted to local, and others where web user location is ignored completely, but this could be just poor report writing from my predecessor.
If it works the way I think it does (disclaimer), then the message sent to a user would be in Orion server local time, but a user viewing a report with said conversion would see user local time.
The second query would take a value stored as a custom property, and use that to convert the time to the local time of the alerting node. This would be one way to do it, assuming the user and the node are in the same time zone. The benefit to using custom properties is that you control it, which is also a con... meaning you have to manage it, and make sure that it's assigned to anything that could alert, or you won't get the time converted like you want it.
The way I've done it in the past is to assume the node and the user live in the same time zone, then you require that the custom property is set when new inventory is added. Then, you can convert the trigger time (UTC or Orion local) to the user/node time zone. Another thing to consider is that alerts will be local time-stamped when they are received in said remote user's inbox, so it might be moot.
