# Query help sql date range between grouping 7am to 7am

**URL:** <https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315>\
**Category:** Ignition\
**Created:** [May 6, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315 "2022-05-06T18:33:42Z")\
**Posts on this page:** 1\
**Showing post:** 35

<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 4, 2022, 9:05pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/35 "2022-10-04T21:05:00Z")

</div>

I posted a _ **comprehensive** _ solution for grouping by shift some time ago (fully dynamic table-driven shift definitions):

> [@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…

It relies on a PostgreSQL table-generating function for efficiency and convenience, but that can be replaced with a recursive common table expression.

The key is, given a timestamp, produce the timestamp for the beginning of the shift that falls in. As a computed column in a subquery. Then the outer query can group on that start-of-shift timestamp.

The comprehensive solution provides a joinable, dynamically-generated set of shift periods that can also be used to truncate timespans.

---

_[View the full topic](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315)._