Knowledge Base

You can record the TIMESTAMP field (as well as the obligatory field CHRONO) when you configure an archive unit in HDS format in PcVue.

The limitation is that this field is only useful for interpreting the Date-time value of a CHRONO field. To avoid registration of the TIMESTAMP field, which would take up space in the SQL Server database, we advise you to use a SQL Server view.

Reminder: Changing the structure of existing HDS tables is not allowed, which is why we create an SQL Server view. That provides a different appearance for a table without modifying its structure. In our case access to a view will limited to read-only mode.

What you need to know:

1. In the present structure of the HDS tables, two fields let you know the date-time value for an entry in UTC format. These two fields let you to identify the seasonal clock changes. You must always use them for export, purging etc. processes.
   
   • Chrono (UTC time in DateTime format) – required
   • TS (UTC time in text format) – optional
2. There is also a DLL for transforming a Chrono value to local time (AIHdsTSConversion.dll) and vice versa. This DLL is programmed in the .NET language for best performance in conversion but it requires prior activation of the CLR code in the SQL Server database.

How to create a view with the local time field

1. In your SQL Server database, install the DLL “AIHdsTSConversion.dll” via the script “Deploy.sql” and its documentation.  
2. Test the installation of this DLL via the script “Test.sql” and its documentation.
3. Create a SQL Server view containing all fields of the Trend and Log tables plus the new LocalTime field.

To set up a view of the table, enter the following request in SSMS:

USE [DATABASE_NAME]
GO
CREATE VIEW [dbo].[TABLE1_VIEW] AS
SELECT *, [dbo].[fn_GetLocalDatetimeFromUTCFiletime](Chrono) AS [LocalTime]
FROM [DATABASE_NAME].[dbo].[TABLE1]
GO

 Where :

• DATABASE_NAME is the name of the database that contains the HDS tables.
• TABLE1 is the name of the table of tendency or log.
• TABLE1_VIEW is the name of the view that you create.

Now that the view is set up, you can request it as if it were a table.

SELECT * FROM [DATABASE_NAME].[dbo].[TABLE1_VIEW]

Download attachments: AI_HdsTsConversion.zip