How to format the "ToLocal" SWQL command

I need to convert the timestamps in a SWQL report to show in local time. I know that the ToLocal command will do that, but I can't tell where the parentheses go. Can anyone provide a before and after example? Thanks.

Top Replies

  • Using the TOLOCAL, you just put that first, then put the date/time field in the parentheses directly afterwards. You'll probably need to assign an alias as well, but it can usually be the same name as the field, or completely different if you'd prefer.

    If you were going to put the "TriggeredDateTime" value from the AlertActive table, it should look something like the following.

    Before/without the function:

    ,AlertActive.TriggeredDateTime

    After/with the function

    ,ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime

    I hope that helps get you what you need.

    Thank you,

    -Will

  • Thanks for the reply wluther, but when I re-examined the following query I'm not sure now why I need the ToLocal after all. My understanding is that GETDATE should be returning the local time anyway (I verified that the Orion server is set to the local time zone).

    Anyway, in the following query I actually have two problems. 1. I need to get the local time as previously mentioned, and 2. I need the report to be able to get three time frames in separate reports - Yesterday, Last Week, and Last Calendar Month. In the query you see I can't seem to get anything other than the current day, week or month. Sorry to throw so much out there, but I am so close to getting this right. Just need to get over the hump.

    SELECT  Nodes.Caption, Nodes.IPAddress, StartTime.EventTime AS DownTime ,
    (
        SELECT TOP 1 EndTime.EventTime
        FROM Orion.Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime
        AND EndTime.EventType = 5
        AND EndTime.NetObjectType = 'N'
        AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime
    ) AS UpTime,
    Nodes.CustomProperties.Customer_Node,
    MINUTEDIFF(StartTime.EventTime,(
            SELECT TOP 1 EventTime FROM Orion.Events AS Endtime
            WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime
    )) AS MinutesDown
    FROM Orion.Events AS StartTime
    INNER JOIN Orion.Nodes ON StartTime.NetworkNode = Nodes.NodeID
    WHERE (StartTime.EventType = 1)
    AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))
    AND Nodes.CustomProperties.Customer_Node = 'True'
    ORDER BY StartTime.EventTime desc

  • GETDATE() does local server time.

    ToLocal() is more for when you want to present it in a browser context: modern dashboard or the like and want to convert it to the local browser time.

  • Interesting given that the report you see above is currently generating UTC timestamps. Any idea why?

  • My understanding (and I'm not one of the architects), is that the ToLocal() will only work when put in a "webby" thing (Modern Dashboard, Report, Custom Query Widget) but in SWQL Studio, it doesn't do any conversions.

    I'm only about 70% sure here on the nuances of this function.

  • Gotcha. So, any thoughts as to why the query above results in UTC timestamps?

  • Some data in the database is stored in UTC time. Some data in the database is stored in the local database time. I can't recall offhand which is which but I recommend confirming via the raw data in the database. 

    E.g. testing an event at 2:23pm for this reply, the EventTime is stored in UTC but I can use TOLOCAL(EventTime) to convert it to my local time. GetDate and GetUTCDate used to verify.

  • Thanks for the ideas folks, but I am still struggling to explain why  the timestamps are displaying in UTC time. I've proven that by manually taking a node down and back up. The events show up on UTC time rather than CST even though the GETDATE is used in the query. Need to fix this. Also, need this report to be YESTERDAY, not TODAY. Thanks.

  • To display it in local time you need to change the select for the date to:

    ToLocal(StartTime.EventTime) AS DownTime

  • Thank you. Is that the only place in my original query that needs the ToLocal command? Do I also need it in the  AND statement 4 lines from the bottom?

    Also, can you help me with the syntax for Yesterday, Last Week and Last Month?