I’m looking to have a timestamp written to a tag the first time a tag changes each day and then written to a 2nd tag the last time it changes each day.
For example: a gate opens 50 times throughout a day. The first time it opens that day would write to tag 1 and the last time it opens would write to tag 2.
Tag 1: 7:06 AM
Tag 2: 4:53 PM
I’m not able to script a duration on tag for this because it opens and closes throughout the day and is not always open. Trying to get an idea of total hours a site was active.
I would consider just setting up tag history for a tag to monitor the value of the status of the gate. Assume a boolean of true means it’s open, you could then run system.tag.queryTagHistory on the tag for a set time period, specifiy the “DurationOn” aggregation mode.
Sorry I probably should have described it a bit better at first. Essentially if I have these two timestamps in a table I can export it and then have a total time calculated as a result. I just can’t figure out how to write a first and last event timestamp to a designated tag
So for the example above the table I output could then calculate the difference between Tag 1 and Tag2 as 9 hr 47 min.
If you are logging the gate status to a historical table when it changes, you can get the min and max timestamps of each day with something like (Warning SQL pseudo code ahead)
Select the min timestamp of GateStatus where GateStatus = Open
Group by Days
Select the max timestamp of GateStatus where GateStatus = Closed
Group by Days
I'm out of SQL practice so I'm not sure if you should Join the two result sets based on day number, or if you can Union them together
Also, obvious question: Is the gate guaranteed not to remain open over midnight?
Essentially if I have these two timestamps in a table
Are they in a SQL table? If so that's easy with MIN() and MAX() like @peter is saying
This will return as two rows:
SELECT MIN(datetimestamp) as first
FROM table
WHERE datetimestamp BETWEEN <start> AND <end>
ORDER BY datetimestamp ASC
UNION ALL
SELECT MIN(datetimestamp) as last
FROM table
WHERE datetimestamp BETWEEN <start> AND <end>
ORDER BY datetimestamp DESC
(or drop the ALL and just do UNION which will combine duplicate entries so you can check if you only have one row returned and cut out the edge case where the gate was left open all day or shut after midnight and not touched for the next 24)
or to have them return as columns
SELECT first.first, last.last FROM
(SELECT MIN(datetimestamp) as first
FROM <table>
WHERE datetimestamp BETWEEN <start> AND <end>
ORDER BY datetimestamp ASC) as first
, (SELECT MAX(datetimestamp) as last
FROM <table>
WHERE datetimestamp BETWEEN <start> AND <end>
ORDER BY datetimestamp DESC) as last)
If the times were recorded in a tag you could historize it and use system.tag.queryTagHistory() with the LastValue aggregation mode, the endDate as midnight and rangeHours to -24 to find the last one. startDate as midnight and rangeHours to 24 for the first time
Also, obvious question: Is the gate guaranteed not to remain open over midnight?
What determines the first and last time of a day? This is a difficult thing to a predict (was it the event at 11:55 or 11:58), which is why you're having trouble.
However, if you record every event, and then poll the data it is quite simple to get the first and last event.
What determines the first and last time of a day? This is a difficult thing to a predict (was it the event at 11:55 or 11:58), which is why you’re having trouble.
However, if you record every event, and then poll the data it is quite simple to get the first and last event.
Yep. And space is cheap. Otherwise, first is the one after rollover and for last you'd have to store a previous value variable and then move that to last at the rollover time.
