1 Reply Latest reply on Nov 15, 2012 2:28 AM by lukas.cerny

    converting time zone for "unmanage_from" and "unmanage_until" fields

    cdenuyl

      I have a report that lists all transactions with a non-null "Unmanage_From" field.  The times for Unmanage_From and Unmanage_Until are in native UTC.  What format filter can I apply to this report so that these date/times display in Eastern Standard Time, as they do in the web GUI?e

        • Re: converting time zone for "unmanage_from" and "unmanage_until" fields
          lukas.cerny

          Hello,

          I'm afraid that reports currently do not support this kind of filter. However reports use specific database view to get data so by modifying this view you could achieve desired functionality. The following SQL script should update the database reports view to automatically convert UTC time to the timezone used by database server.

          SET QUOTED_IDENTIFIER ON
          GO
          
          IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SEUM_TransactionsReportsView' AND TABLE_TYPE = 'VIEW' )
          BEGIN
               DROP VIEW SEUM_TransactionsReportsView
          END
          
          GO
          
          CREATE VIEW [dbo].[SEUM_TransactionsReportsView]
          AS
          
               SELECT  t.[TransactionId],
                         t.[RecordingId],
                         t.[AgentId],
                         t.[Name],
                         t.[Description],
                         t.[Frequency] AS [PlaybackInterval],
                         ~t.[Unmanaged] AS [IsLicensed],
                         t.[Unmanaged],
                         CASE WHEN t.[UnmanageFrom] >= '1900-1-1' 
                         THEN DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), t.[UnmanageFrom]) 
                         ELSE NULL END AS [UnmanageFrom],
                         CASE WHEN t.[UnmanageUntil] >= '1900-1-1' 
                         THEN DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), t.[UnmanageUntil]) 
                         ELSE NULL END AS [UnmanageUntil],
                         s.[ShortDescription] AS [LastStatus],
                         t.[LastErrorMessage],
                         ROUND(t.[LastDuration]/1000.0, 2) AS [LastDuration],
                         CASE WHEN t.[LastDateTimeUtc] >= '1900-1-1' 
                         THEN DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), t.[LastDateTimeUtc]) 
                         ELSE NULL END AS [LastPlayed]
               FROM [dbo].[SEUM_Transactions] t 
               LEFT JOIN [dbo].[StatusInfo] s
                    ON t.[LastStatus] = s.[StatusId]
          

          I've also added request for doing this conversion by default to our internal tracking system.