# Timestamp First and Last Tag Event Each Day **URL:** \ **Category:** Ignition\ **Created:** [February 25, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843 "2021-02-25T19:38:17Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![bennettr](https://avatars.discourse-cdn.com/v4/letter/b/0ea827/32.png) [@bennettr](https://forum.inductiveautomation.com/u/bennettr)\ **Post date:** [February 25, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/1 "2021-02-25T19:38:17Z")
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. Thanks for the help! ---
**Author:** ![Paullys50](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paullys50/32/3324_2.png) [@Paullys50](https://forum.inductiveautomation.com/u/Paullys50)\ **Post date:** [February 25, 2021, 8:00pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/2 "2021-02-25T20:00:04Z")
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. ---
**Author:** ![bennettr](https://avatars.discourse-cdn.com/v4/letter/b/0ea827/32.png) [@bennettr](https://forum.inductiveautomation.com/u/bennettr)\ **Post date:** [February 25, 2021, 8:09pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/3 "2021-02-25T20:09:19Z")
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. ---
**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\ **Post date:** [February 25, 2021, 8:25pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/4 "2021-02-25T20:25:52Z")
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? ---
**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\ **Post date:** [February 25, 2021, 8:41pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/5 "2021-02-25T20:41:54Z")
> 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: ```sql SELECT MIN(datetimestamp) as first FROM table WHERE datetimestamp BETWEEN AND ORDER BY datetimestamp ASC UNION ALL SELECT MIN(datetimestamp) as last FROM table WHERE datetimestamp BETWEEN AND 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 ```auto SELECT first.first, last.last FROM (SELECT MIN(datetimestamp) as first FROM WHERE datetimestamp BETWEEN AND ORDER BY datetimestamp ASC) as first , (SELECT MAX(datetimestamp) as last FROM
WHERE datetimestamp BETWEEN AND 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? Best not to think about these types of things ---
**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\ **Post date:** [February 25, 2021, 8:47pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/6 "2021-02-25T20:47:10Z")
> [@chasondeshotel](#): > > > Also, obvious question: Is the gate guaranteed not to remain open over midnight? > > Best not to think about these types of things Not questioning assumptions is the best way to get an invalid result 😉 ---
**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\ **Post date:** [February 25, 2021, 8:57pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/7 "2021-02-25T20:57:09Z")
True, true. In that case I would make sure any HMIs are far away from the gate. It’s not invalid as long as it’s difficult to prove ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [February 25, 2021, 9:03pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/8 "2021-02-25T21:03:17Z")
> [@bennettr](#): > > I just can’t figure out how to write a first and last event timestamp to a designated tag 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. > [@chasondeshotel](#): > > True, true. In that case I would make sure any HMIs are far away from the gate. It’s not invalid as long as it’s difficult to prove No it's still invalid, its just assumed valid. 🤣 ---
**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\ **Post date:** [February 25, 2021, 9:11pm UTC](https://forum.inductiveautomation.com/t/timestamp-first-and-last-tag-event-each-day/43843/9 "2021-02-25T21:11:06Z")
> 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. > No it’s still invalid, its just assumed valid. 🤣 It's not a lie if you believe it hard enough