Just FYI, this is the historian table structure:
Ignition Historian Tables - dbdiagram.io
From what you’ve said, you can use the tag historian, however if it were me, I would be creating a new table in SQL to store this data. I would be inserting a row every time a new part is counted so that you would end up with a table that holds the timestamps (in unix epoch ms) of each part that has been processed.
E.g. the data might look like this:
id timestamp partNo tagPath
1 1631739011000 120394 path/to/tag
2 1631739722000 100334 path/to/another/tag
You should really create at least two tables, one that stores the data and one that stores the tags, same as how the Ignition historian tables are defined where the data table links the tags by their ids.
Timestamps should be stored in unix epoch (i.e. UTC time as a bigint) to avoid DST and to make sure querying across different timezones doesn’t affect the results. You can easily get this from script using:
currentValue.timestamp.getTime() # converts tag's timestamp into unix epoch in ms
The id column should be auto-incrementing
To do all of this, I would use tag change scripts attached to each of your tags to monitor for changes, and if non-0, then insert a row into the data table. Make sure that you’re not inserting if initialChange is on in the change event. Then your query becomes much simpler.
SELECT
CONVERT(varchar, dbo.fn_ConvertToDateTime(timestamp, 1), 112) as yyyymmdd,
count(*) as parts_count
FROM
datPartsProcessed
WHERE
dbo.fn_ConvertToDateTime(timestamp, 1) BETWEEN DATEADD(Month, -1, GETDATE()) AND GETDATE()
AND
DATEPART(hour, dbo.fn_ConvertToDateTime(timestamp, 1)) BETWEEN 7 AND 15
GROUP BY
CONVERT(varchar, dbo.fn_ConvertToDateTime(timestamp, 1), 112) -- converts ts to yyyymmdd
where you’ll need to add this function into your database to convert a unix timestamp into a local datetime:
/* SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
DECLARE @LocalTimeOffset BIGINT
,@AdjustedLocalDatetime BIGINT;
SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
RETURN (SELECT DATEADD(ms,@AdjustedLocalDatetime%60000, DATEADD(minute,@AdjustedLocalDatetime/60000, CAST('1970-01-01 00:00:00' AS datetime))))
END;
GO*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_ConvertToDateTime] (@Datetime BIGINT, @ConvertToLocal bit)
RETURNS DATETIME2(7)
AS
BEGIN
DECLARE @LocalTimeOffset BIGINT
,@AdjustedLocalDatetimeSec BIGINT
,@Milliseconds BIGINT;
SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
SET @AdjustedLocalDatetimeSec = @Datetime / 1000 -- Convert to seconds
SET @Milliseconds = @Datetime % 1000
IF @ConvertToLocal = 1
SET @AdjustedLocalDatetimeSec = @AdjustedLocalDatetimeSec - @LocalTimeOffset;
RETURN (SELECT DATEADD(millisecond, @Milliseconds, DATEADD(second,@AdjustedLocalDatetimeSec, CAST('1970-01-01 00:00:00.000000' AS datetime2(7)))))
END;
**All completely untested
Also assuming you’re using T-SQL (MS SQL Server)
