Looking for guidance on an efficient way to log tag data at specified intervals to be easily queried for reports.
Currently I have UDT for bins, one of the tags in the UDT is a string for bin status. I would like to log this tag every hour on the hour. I would then like query a row for each hour and only pull those that meet a certain status like "In Use", etc
Any advice on how to best insert this data into a table to be easily queried without having to do individual queries for each hour or each bin? There are about 40 bins, and I'm looking for an efficient way to insert this data as well as pull it into a report.
I'm basically wanted to add another column that has all the bins that had statuses of "In Use"
Can you not use tag historian?
I would like to log this tag every hour on the hour.
I recommend creating a dedicated database table for this and use a Scripting â Gateway Events â Scheduled â Once Per Hour (0 * * * *) script to log the data.
... and only pull those that meet a certain status like "In Use", etc.
Do that filtering at the time of data retrieval by filtering in the query.
The resultant table gives you great flexibility in reporting current and future requirements.
I can't make out from your sample data whether or not you are using totalising counters or incremental. Storing the totalising (non-resetting) counters makes it easy to calculate differences and averages between any two timestamps.
3 Likes
Is there an advantage to using a gateway script over a transaction group?
For the totalizer data I am actually doing both recording the totalizer and selecting the last row and storing the difference.
I think where I am getting hung up is on how to setup my schema to where my query returns the columns. The easiest way is to have the data injected to look like this. What I want is the result from my query to return the column names of certain "statuses" that are stored in the row.
This screenshot probably explains better what I am trying to achieve.
Is there an advantage to using a gateway script over a transaction group?
With the gateway scheduled events I'm confident that they will run on the minute I specify - :00 in your case. With transaction groups I'm not sure. You have it set to run once per hour but I don't see any promise that it will run on the hour rather than, say, at 23 minutes past.
With the scheduled script you'd also have the option to round the timestamp down to the minute so that your timestamps are exactly xx:00:00. This may help in the data retrieval.
What I want is the result from my query to return the column names of certain "statuses" that are stored in the row.
This will get you most of the way to what you want. (I've assumed MySQL which has a concat-with-separator function.)
SELECT t_stamp,
CONCAT_WS(
', ',
IF(B1 = 'enabled', 'B1', NULL),
IF(B2 = 'enabled', 'B2', NULL),
IF(B3 = 'enabled', 'B3', NULL),
IF(B4 = 'enabled', 'B4', NULL)
) AS BinsEnabled
FROM SampleData;
Have a play with it here:
Note that timestamp is a reserved word in some SQL variants. It's not a great name for a column.
2 Likes
This query should give you what you want.
SQL query 2
SELECT
t_stamp,
CONCAT_WS("|", B1, B2, B3, B4) AS contents, -- Just for debugging.
IF(
( IF(B1 = 'enabled', 1, 0) +
IF(B2 = 'enabled', 1, 0) +
IF(B3 = 'enabled', 1, 0) +
IF(B4 = 'enabled', 1, 0)
) = 0,
'-----', -- String to return if no columns are 'enabled'.
CONCAT_WS(
', ',
IF(B1 = 'enabled', 'B1', NULL),
IF(B2 = 'enabled', 'B2', NULL),
IF(B3 = 'enabled', 'B3', NULL),
IF(B4 = 'enabled', 'B4', NULL)
)
) AS BinsEnabled
FROM SampleData;
Play with the query here:
For posterity, this is guaranteed for transaction groups in schedule mode as much as it is for gateway scheduled scripts:
2 Likes
