# Tag Historical data - frequency and duration of tag values

**URL:** <https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667>\
**Category:** Ignition\
**Tags:** vision, scripting\
**Created:** [October 25, 2022, 6:02pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667 "2022-10-25T18:02:53Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 25, 2022, 7:25pm UTC](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667/9 "2022-10-25T19:25:40Z")

</div>

This not a job for the tag historian. Events should be recorded in a dedicated table with timestamp, preferably as just the numeric code. A transaction group would be fine, or just a tag change event script. A separate table should have the association between code and text. Don't record the text (terribly wasteful of DB space).

Once you have events recorded, your database's `lead()` or `lag()` functions can help you obtain precise intervals, which the database can then `sum()`, with or without splitting intervals across shift boundaries. The shift boundaries can come from other tables, possibly with support for rotating shifts (also in the DB). The DB's `lead()` or `lag()` functions can also clean up any intervals where disruptions cause the same value to be recorded as the previous one.

Some of the DB operations can be complex, but regular scheduled maintenance operations can insert the results into pre-computed aggregate tables most useful for reports.

There are many ways to compute pieces of this task. All others are nightmares to assemble into the final result you indicate you want. This is a database task. Use the database.

For durations from event recordings in a time interval, see this:

> [@Calculating Total Time in Seconds](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/28):
>
> Put the following into a named query, with value parameters begin\_ts and end\_ts of type DateTime: SELECT stopCode, count(stopCode) as Qty, sum(CASE WHEN t\_stamp1 \> :end\_ts THEN :end\_ts ELSE t\_stamp1 END - CASE WHEN t\_stamp \< :begin\_ts THEN :begin\_ts ELSE t\_stamp END) AS Duration FROM ( SELECT t\_stamp, stopCode, coalesce(lead(t\_stamp) OVER (ORDER BY t\_stamp), CASE WHEN current\_timestamp \< :end\_ts THEN current\_timestamp ELSE :end\_ts END) AS t\_stamp1 WHERE t\_stamp \>= coalesce(( SELE…

For rotating shifts, this is a comprehensive solution:

> [@Script for periodically changing shift time](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3):
>
> Hi Berkay, I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example: create table shift\_schedules (shift\_id serial, shift\_name varchar(255), refdate date, repeat int, tzname varchar(255), primary key (shift\_id)); insert into shift\_schedules (shift\_name, refdate, repeat, tzname) values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/Ne…

---

_[View the full topic](https://forum.inductiveautomation.com/t/tag-historical-data-frequency-and-duration-of-tag-values/65667)._