# Machine states durations grouped by time in SQL best options

**URL:** <https://forum.inductiveautomation.com/t/machine-states-durations-grouped-by-time-in-sql-best-options/86822>\
**Category:** General Discussion\
**Created:** [March 5, 2024, 12:57pm UTC](https://forum.inductiveautomation.com/t/machine-states-durations-grouped-by-time-in-sql-best-options/86822 "2024-03-05T12:57:11Z")\
**Posts on this page:** 1\
**Showing post:** 3

<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:** [March 5, 2024, 1:18pm UTC](https://forum.inductiveautomation.com/t/machine-states-durations-grouped-by-time-in-sql-best-options/86822/3 "2024-03-05T13:18:15Z")

</div>

Sounds like you've correctly identified the key factors here.

I usually use a table-generating function to produce the sequence of boundary timestamps, then either lag() or lead() on those to get `grp_start_ts` and `grp_end_ts` **per period** in the row together.

Separately (separate subquery), I gather the events as you describe, using lead() to get the `ev_start_ts` and `ev_end_ts` for each. Starting with the last event **before** the overall time span ensures that you have the event that spans that first boundary.

I then left join the groups subquery with the events subquery, capturing every event for a period where it **overlaps**. Like so:

```auto
... ON periods.grp_start_ts < events.ev_end_ts AND events.ev_start_ts < periods.grp_end_ts

```

This duplicates event rows into all periods where they overlap. This allows you to truncate to the period boundaries to get the right value in the period, without losing the overall event length.

Your outer query would sum the truncated durations, grouping by `grp_start_ts`.

If you have complex shift scheduling that you want to handle, consider using something like this for the period generation:

> [@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/machine-states-durations-grouped-by-time-in-sql-best-options/86822)._