Return to Question

I really hope this isn't a stupid question. As i researched my problem, I was just getting more and more confused as I'm not an expert in database topics.

Context:

There is a measurement system that is measuring values like temperature and stores them in a database. There is one value stored every minute. My task is to access these values. So far, so easy, theoretically...

Where is the Problem?

There isn't one value for each minute. The table with the measurement data condenses ten values into one row, which is indicated by a "ValueCount"-Column with a Value of "10" in each row. For the actual "Value"-Column I can just see "(BLOB)". After a bit of research I found the datatype of the "Value"-Column is "varbinary(max)". So i tried to convert it via sql, this is the result for one example value:

SELECT CONVERT(nvarchar(max), value, 0) FROM mydatabase

The first result looks like it is structured in 10 packages, what would fit to my number of measurements. But I couldn't find a solution on how to get readable data.

Addtion 06.07.2018 13:00

first of all, thank you for the detailled reply. The fact that we can see 10 values by reformatting indicates that we are on the right track, hopefully. But I try to provide more usefull information about the database and the data I expect in the varbinary.

"Database Structure"

The table that contains the varbinary looks like this:

Header:  analogChannelRef | startTime | stopTime | valueCount | value     | crc
DBKey:   PK, FK           | PK        |          |            |           |
Type:    bigint           | datetime  | datetime | int        | varbinary | int

The value in "analogChannelRef" represents the measurement properties. The measurement properties are further detailled in an other table (for example name, unit and precision of the measurement). The difference between "startTime" and "stopTime" is always 10 minutes and the value in "valueCount" is always 10. Hence there is one measurement per Minute, but all 10 measuremnts are stored into the varbinary in "value".

What is actually stored in the varbinary?

There is an other table in the database where min/max/average-Values are stored daily. In that case the values are not stored as varbinary but as float (example: 84,5321426391602). The connection data that connects the measurement system with the database is stored in a seperate table in the database, so I don't think there should be any connection information in "value".

In my opinion the varbinary should contain 10 float Values. Perhaps the datetime is also included for each float, but that would be redundant information since start/stop-Time and valueCount would already cover that information.

I hope this information gives you a better picture about the situation,

It has been quite a long time since I asked this question so I want to share my progress with you.

Introduction and Context:

There is a measurement system that is measuring 20 different values like temperature or pressure every minute and stores them into a database. My task is to access these values directly. So far, so easy, theoretically...

Tables of Interest in the Database

In order to access the measurement values directly, I only need two tables in the database. The measurements are saved in the first table. Every measurement has a start- and stoptime and a discrete number of measurementvalues ("valueCount") stored in the column "value":

Header:  analogChannelRef | startTime           | stopTime            | valueCount | value     | crc
DBKey:   PK, FK           | PK                  |                     |            |           |
Type:    bigint           | datetime            | datetime            | int        | varbinary | int
Exmpl:   10181            | 03.08.2018 00:01:00 | 03.08.2018 00:10:00 | 10         | (BLOB)    | 61776375

The column "analogChannelRef" is in a relationship to the second Table that contains informations about the measured value. The column "channel number" states which input of the the measurement system is used for a specific measurement. The columns "tag" and "unit" are used to give further informations about the measurement. The column "precision" defines how many decimals are shown to the enduser:

Header:  id     | channel number | tag           | unit          | precision   | crc
DBKey:   PK     |                |               |               |             |
Type:    bigint | int            | nvarchar(255) | nvarchar(255) | tinyint     | int
Exmpl:   10181  | 15             | Pressure      | bar           | 2           | -740224624

So, where is the Problem?

There isn't one value for each minute, so i cannot simply read out the values. The table with the measurement data condenses ten values into one row, which is indicated by a "ValueCount"-Column with a Value of "10" in each row. For the actual "Value"-Column I can just see "(BLOB)". After a bit of research I found the datatype of the "Value"-Column is "varbinary(max)". So i tried to convert it via sql, this is the result for one example value:

SELECT CONVERT(nvarchar(max), value, 0) FROM mydatabase

As Max Vernon answered (thanks again your help), it looks like the result is structured in 10 packages, what would fit to the number of measurements stored into one "value"-cell. But I couldn't find a solution on how to get readable data based on that information.

Possible Solution

To find a solution on how to get the actual values, I further investigated the Microsoft SQL Server Management Studio and found so called "procedures". After a bit of research on procedures I found one that looked really promesing for my task:

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[report_getAnalogvalue](
    @channelId BIGINT,
    @startDate DATETIME,
    @endDate DATETIME,
    @configurationId BIGINT,
    @userId BIGINT,
    @maxNumber BIGINT)
AS
    DECLARE @AnalogValueTable TABLE
    (
        "timestamp" DATETIME, 
        "timestampoffset" BIGINT,
        "value" INT,
        "valueState" VARBINARY, 
        "dataIntegrity" TINYINT -- 1 = true, 0 = false
    )

    DECLARE @values VARBINARY(MAX),
            @record_length INT,
            @valueCount INT,
            @crc INT,

            @record_data VARBINARY(23),
            @record_timestamp DATETIME,
            @record_value VARBINARY(4),
            @record_valuestate VARBINARY,
            @record_timestampoffset BIGINT
    
    DECLARE @record_dataIntegrity TINYINT
    SET @record_dataIntegrity = 0

    DECLARE @rawTime VARBINARY(8)

    DECLARE @record_counter BIGINT
    SET @record_counter = 1

    DECLARE @Ticks BIGINT,
            @Days FLOAT

    DECLARE @isAdministrator BIT
    SET @isAdministrator = 0
BEGIN
    SET NOCOUNT ON  

    EXECUTE @isAdministrator = report_checkAccess @configurationId, @userId

    DECLARE c CURSOR FOR
            SELECT a.value, a.valueCount, a.crc
            FROM "AnalogValue" a INNER JOIN "AnalogChannel" b ON a."analogChannelRef" = b."id"              
            WHERE a."analogChannelRef" = @channelId
                AND (@startDate BETWEEN a."startTime" AND  a."stopTime" 
                    OR @endDate BETWEEN a."startTime" AND  a."stopTime" 
                    OR a."startTime" BETWEEN @startDate AND @endDate
                    OR a."stopTime" BETWEEN @startDate AND @endDate)
                AND (b."configurationRef" = @configurationId)
                AND (@isAdministrator = 1)
    OPEN c

    FETCH NEXT FROM c INTO @values, @valueCount, @crc

    WHILE @@FETCH_STATUS = 0 BEGIN
        SET @record_length = DATALENGTH(@values)
        IF (@record_length <> @valueCount * 23 + 1 OR @record_length < 24)
            BREAK

        IF dbo.GET_CRC32(@values) = @crc
            SET @record_dataIntegrity = 1

        SET @values = SUBSTRING(@values, 2, @record_length-1)
        SET @record_length = DATALENGTH(@values)
        WHILE (@record_length >= 23)
        BEGIN
            IF (@record_counter > @maxNumber)
            BEGIN
                CLOSE c
                DEALLOCATE c

                SELECT * FROM @AnalogValueTable

                RETURN
            END
            
            SET @record_data = SUBSTRING(@values, 1, 23)
            SET @rawTime = SUBSTRING(@record_data, 1, 8)
            EXECUTE ParseTimestamp @rawTime, @timestamp=@record_timestamp OUTPUT

            IF (@record_timestamp > @endDate)
                BREAK
            
            IF (@record_timestamp BETWEEN @startDate AND @endDate)
            BEGIN
                -- use REVERSE to convert from little endian to big endian
                SET @record_value = CAST(CONVERT(BINARY(4), REVERSE(SUBSTRING(@record_data, 10, 4))) AS INT)                
                SET @record_valuestate = SUBSTRING(@record_data, 14, 1)
                
                -- use reverse to convert from little endian to big endian
                SET @record_timestampoffset = CAST(CONVERT (BINARY(8), REVERSE (SUBSTRING(@record_data, 16, 8))) AS BIGINT)
                    
                INSERT INTO @AnalogValueTable("timestamp", timestampoffset, value, valueState, dataIntegrity) 
                VALUES(@record_timestamp, @record_timestampoffset, @record_value, @record_valuestate, @record_dataIntegrity)

                SET @record_counter = @record_counter + 1
            END

            SET @record_length = @record_length - 23
            SET @values = SUBSTRING(@values, 24, @record_length)
        END

        FETCH NEXT FROM c INTO @values, @valueCount, @crc
    END

    CLOSE c
    DEALLOCATE c

    SELECT * FROM @AnalogValueTable
END

If I call this procedure with the right input parameters I actually get values the values for each minute.

So my first question is: How does this procedure work?

So, everything is fine?

I'm not sure. The Values that I get by calling the procedure are in form of Integers. But my measurements are numbers like 80,51 °C. So I have to devide the Integer I get by a specific number to get the real value.

Hence, my second question is: Why are the Values stored as Integers?

Finally, the correlation between the Integer-Values and the real Values is quite interesting. In order to find the correlation, I plotted the Integer-Values over the real Values. I expected a linear function, but i got multiply linear functions for specific values of the real value:

These regions seem to follow a pattern: 0,5 - 1; 1 - 2; 2 - 4; 4 - 8; and so on.

So my last question is: Is there any reason, from the perspective of an database expert, to store values like this?

I hope this information gives you a better picture about the situation.

I hope this information gives you a better picture about the situation,

Hopefully you can figure out what I did wrong.

Hope you have a great day,

Addtion 06.07.2018 13:00

first of all, thank you for the detailled reply. The fact that we can see 10 values by reformatting indicates that we are on the right track, hopefully. But I try to provide more usefull information about the database and the data I expect in the varbinary.

"Database Structure"

The table that contains the varbinary looks like this:

Header:  analogChannelRef | startTime | stopTime | valueCount | value     | crc
DBKey:   PK, FK           | PK        |          |            |           |
Type:    bigint           | datetime  | datetime | int        | varbinary | int

The value in "analogChannelRef" represents the measurement properties. The measurement properties are further detailled in an other table (for example name, unit and precision of the measurement). The difference between "startTime" and "stopTime" is always 10 minutes and the value in "valueCount" is always 10. Hence there is one measurement per Minute, but all 10 measuremnts are stored into the varbinary in "value".

What is actually stored in the varbinary?

There is an other table in the database where min/max/average-Values are stored daily. In that case the values are not stored as varbinary but as float (example: 84,5321426391602). The connection data that connects the measurement system with the database is stored in a seperate table in the database, so I don't think there should be any connection information in "value".

In my opinion the varbinary should contain 10 float Values. Perhaps the datetime is also included for each float, but that would be redundant information since start/stop-Time and valueCount would already cover that information.

I hope this information gives you a better picture about the situation.