# 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:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 6, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/1 "2022-05-06T18:33:42Z")

</div>

all the queries I find are close, but not right

So I think there is a way using datepart, between, and my t\_stamp to group by a range of 7am to 7am

I thought like

```auto
select 
line,
hours,
 t_stamp between ( dateadd(hour,7, convert(datetime,convert(date,t_stamp)) ), dateadd(hour,7,dateadd(day,-1,convert(datetime,convert(date,t_stamp))))) as this

from mytable
group by line, hours, this

```

is something like this possible?

I have grouped by year month before like

```auto
select format(t_stamp, 'yyyy-MMM') as year_month

group by format(t_stamp, 'yyyy-MMM') as year_month

```

I am not sure how to get this result though.

---

<div class="post-metadata">

**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:** [May 6, 2022, 7:47pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/2 "2022-05-06T19:47:08Z")

</div>

You can not use the `BETWEEN` operator outside of a `WHERE` clause.

If you want to GROUP by a RANGE, then you would need to generate a list of the appropriate ranges, and then LEFT JOIN to that list. Not what I would call trivial.

You could group by a date part.

```auto
SELECT line,hours,datepart(HOUR,t_stamp) AS 'Hour'
FROM mytable
GROUP BY line, hours, datepart(HOUR,t_stamp)

```

Or you can select between a specific datetime

```auto
SELECT line,hours
FROM mytabl
WHERE t_stamp BETWEEN @starttime and @endtime
GROUP BY line,hours

```

I’m not sure that either of those is really what you’re looking for, but I’m also not sure what your looking for in general.

What I think you want is data group by line,hours, 7am - 7am over a range of dates. I would probably resort to pulling all data over the range I wanted and then doing the grouping in a script. Maybe not the best solution but it is what I know.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 6, 2022, 8:01pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/3 "2022-05-06T20:01:30Z")

</div>

```auto
select hours,
datemagic
from mytable
group by datemagic

```

if input is like

```auto
3,Wed Mar 23 20:40:40 EDT 2022
3,Wed Mar 23 20:40:39 EDT 2022
4,Wed Mar 23 03:36:10 EDT 2022
3,Wed Mar 22 15:46:44 EST 2022
3,Tue Mar 22 04:16:52 EST 2022
4,Sat Mar 22 03:13:08 EDT 2022
3,Sat Mar 22 03:13:05 EDT 2022
4,Sat Mar 21 04:10:36 EDT 2022

```

output should be like

```auto
 6, Mar 23
 7, Mar 22
10, Mar 21
 4, Mar 20 

```

whatever that datemagic is, I need to learn it

---

<div class="post-metadata">

**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:** [May 6, 2022, 8:07pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/4 "2022-05-06T20:07:44Z")

</div>

This would generate the output you have given, from the input you gave. But it is agnostic to the time. As soon as the day rolls over hours recorded from then to the next time it rolls over will be summed.

```auto
SELECT sum(hours),format(t_stamp,'MMM DD')
FROM mytable
GROUP BY format(t_stamp,'MMM DD')

```

If hours is not included in the GROUP BY, then it must be in an aggregate in the SELECT.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 6, 2022, 8:28pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/5 "2022-05-06T20:28:07Z")

</div>

> [@lrose](#):
>
> ```auto
> SELECT sum(hours),format(t_stamp,'MMM dd')
> FROM mytable
> GROUP BY format(t_stamp,'MMM dd')
> 
> ```

I think it adds up values from last year.  
I think it adds up values from midnight to midnight.

---

<div class="post-metadata">

**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:** [May 6, 2022, 8:35pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/6 "2022-05-06T20:35:33Z")

</div>

It does.

As I said.

> [@lrose](#):
>
> But it is agnostic to the time. As soon as the day rolls over hours recorded from then to the next time it rolls over will be summed.

WHERE clauses are used to `NARROW` a search. GROUP BY is used to AGGREGATE based on some criteria which defines a GROUP within the search.

For instance you could do:

```auto
SELECT sum(hours), datepart(DAY,t_stamp)
FROM mytable
WHERE datepart(YEAR,t_stamp) = 2022
GROUP BY datepart(DAY,t_stamp)

```

This would sum the hours for each day in the year 2022

You have to define what data you want to GROUP over, otherwise it will GROUP over all data.

> **[SQL Server DATEPART() Function](https://www.w3schools.com/sql/func_sqlserver_datepart.asp)**
>
> W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [May 7, 2022, 9:38pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/7 "2022-05-07T21:38:46Z")

</div>

Could you subtract the time to change the starting time of the day in your group by? E. G. Subtract 7 hrs so that the group by still works as per normal grouping by the day year?  
If you ever have multiple shifts I woukd use a case and for each shift check the time is \>= and \< a certain hour and assigning shift numbers for each then grouping on that

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 10, 2022, 12:38pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/8 "2022-05-10T12:38:39Z")

</div>

I had a similar idea on Saturday

I am going to try something like:

`format(dateadd(hr,-8,t_stamp),yyyy-mmm-dd)`

I will be happily surprised if it works. Stack Exchange has threads struggling a decade with the issue.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 10, 2022, 2:15pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/9 "2022-05-10T14:15:27Z")

</div>

I am trying to work on this, but when I use dateadd(), I am only getting 1-12 for hours with hh

when i look up the function though, I don’t see how to get the 1-24, military time.

update: adjusting the start time 8 hours is working, but i couldnt get the format in a format to produce the military time on the dateadd

---

<div class="post-metadata">

**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:** [May 10, 2022, 2:30pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/10 "2022-05-10T14:30:34Z")

</div>

when you run this: `dateadd(hr,-8,t_stamp)` you are telling SQL to subtract 8 hours from t\_stamp. However the date is presented in the end is irrelevant, an hour is an hour independent of if you are working with a 12-hour clock or a 24-hour clock.

For instance both `dateadd(hr,-8,5/10/2022 3:00 PM)` and `dateadd(hr,-8,5/10/2022 15:00)` will both yield a result of `5/10/2022 7:00`. How that time is formatted and presented to the user is up to you.

Try this (assuming that `t_stamp` is a datetime type):

```auto
format(dateadd(hr,-8,t_stamp),'yyyy-mmm-dd HH:mm:ss')

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 10, 2022, 3:55pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/11 "2022-05-10T15:55:56Z")

</div>

Anything between midnight and 7am are considered the previous day, correct? Then a CASE statement should work for you.

```sql
SELECT SUM(hours), d_stamp
FROM (SELECT hours, FORMAT(CASE WHEN DATEPART(hour, t_stamp)<7 THEN DATEADD(day, -1, t_stamp) ELSE t_stamp END, 'yyyy MMM dd') as d_stamp
      FROM mytable
      WHERE t_stamp >= '2022-01-01 07:00:00' and t_stamp < '2022-01-31 07:00:00') subq
group by d_stamp, hours
order by d_stamp, hours

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 10, 2022, 4:00pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/12 "2022-05-10T16:00:40Z")

</div>

Thanks, I see how that would work too.  
That is more flexible, so I could gather up different time frames than days, and mask them as days to use format to group them up.

For today with this application I am using something like:

```auto
select 
line,
net,
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day
from mytable
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), line, net

```

I was messing with the military time to verify this was working right.  
But, I found a way to add another value I track daily, and compare that.

Edit to simplify example, changed hours column to be net column for clarity.

Thanks for the additional way with the more adaptability.

---

<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:** [May 11, 2022, 10:42am UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/13 "2022-05-11T10:42:12Z")

</div>

At some point you are going to be asked to collect and summarize by rotating shifts with arbitrary rotation patterns. It can get complicated. The following generic solution may help you create the correct joins against your production records:

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

You'll have to tweak it for use with SQL Server.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 11, 2022, 1:24pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/14 "2022-05-11T13:24:16Z")

</div>

Switching work shift every week seem so awful, their Mondays are way worse than mine imo.

I thought:  
subquery select to define weeks from modulus of 3 from a start week  
select case statements to group shift and week combo

It would be less efficient than the function?  
I don’t like SQL functions because I can’t see them from within Ignition.

What do the `$$` do in the code?  
What is the “sid”?  
There are some double colons in some of the selects, what do they do?

I appreciate you showing it to me, and I’d like to learn the tradeoffs

---

<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:** [May 11, 2022, 1:31pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/15 "2022-05-11T13:31:25Z")

</div>

> [@zacharyw.larson](#):
>
> What do the `$` do in the code?

They are the start/end markers for the procedure code. SQL Server is a bit different, IIRC.

> [@zacharyw.larson](#):
>
> There are some double colons in some of the selects, what do they do?

They are SQL-standard alternate syntax for CAST(). `a::b` is the same as `CAST(a AS b)`.

---

<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:** [May 11, 2022, 1:32pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/16 "2022-05-11T13:32:37Z")

</div>

> [@zacharyw.larson](#):
>
> What is the “sid”?

Short for `shift_id` within the generator function.

---

<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:** [May 11, 2022, 1:37pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/17 "2022-05-11T13:37:04Z")

</div>

The tweaking for SQL Server will require a substitute for PostgreSQL’s built-in `generate_series()`. I think SQL Server expects you to use a recursive Common Table Expression for such things.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 11, 2022, 1:56pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/18 "2022-05-11T13:56:08Z")

</div>

I need to join the query I worked on from this thread to the one you helped me with in another thread  
[here](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/71)

As much as the duration query looks at t\_stamp, when I join it, I can still join on t\_stamp, and the duration query’s references to t\_stamp for getting time won’t be impacted?  
Or do i join on like machine name, and then will that mean references to like durationTable.t\_stamp will not be impacted?  
The queries are so big, that it is hard for me to retain how they are in my mind.

I join on the inner most table?

so far my attempts to join have been unsuccessful like not compiling

or is there a way to join the tables on `format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd')`

like joining on a subquery, is it a thing you can do?

* * *

I thought recursion was to code fast at the cost of efficiency, and should be avoided.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 11, 2022, 2:38pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/19 "2022-05-11T14:38:34Z")

</div>

oh I can join on a subquery

[SQL LEFT JOIN Subquery Alias - Stack Overflow](https://stackoverflow.com/questions/16776176/sql-left-join-subquery-alias?msclkid=dde8149bd13711ecb8c23d42bad5cd64)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 11, 2022, 3:57pm UTC](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315/20 "2022-05-11T15:57:08Z")

</div>

I am trying to join two tables that are kind of like these:  
(these are simplified versions so I can understand the complexity of the interfacing, yet nothing top secret so to speak easy, is disturbed)

```auto
select 
format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day
, line
, sum(isnull(t.net,0)) as Net
, sum(isnull(t.hours,0)) as Hours
from(
       select cast(right(autoTable.line, len(autoTable.line)-2) as int) as line
       , autoTable.t_stamp
       , Row_Number() over (partition by manualTable.line, autoTable.t_stamp order by autotable.t_stamp desc) as row,
       , isnull(autoTable.net) as net
       , isnull(manualTable.hours,0) as hours
       from autoTable left join manualtable on
       cast(right(autoTable.line, len(autoTable.line)-2) as int) = case when manualTable.line not like '%k' 
       then cast(right(manualTable.line, len(manualTable.line)-2) as int) end)
       and format(autoTable.t_stamp, 'MM/dd/yy') = Format(manualTable.recordDate,'MM/dd/yy')
       --and shift joining magic not listed here

       where autoTable.line='B2' and manaulTable.hours>0
       and autoTable.t_stamp between DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) AND DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
        group by --all that stuff
)t
where t.row<2 -- eliminates duplicates manual entries while allowing review of all manual entries, not a removable feature
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), line
order by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') desc

```

and then another complicated table

```auto
select format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') as Day, sum(datediff(second,sub2.t_stamp,lead)) as Seconds, sum(datediff(second,sub2.t_stamp,lead))/60.0/60.0 as hrs	 
FROM (
	SELECT sub1.line, 
			sub1.code, 
			case when sub1.t_stamp < DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) then DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) else sub1.t_stamp end as t_stamp, 
			coalesce(lead(sub1.t_stamp) OVER (ORDER BY sub1.t_stamp),	CASE WHEN current_timestamp < DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) THEN current_timestamp ELSE DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) END) AS lead
	from(	
		select line, code, eventTable.t_stamp as t_stamp
		from eventTable 
		WHERE t_stamp >= coalesce((
					SELECT TOP 1 t_stamp FROM eventTable
					WHERE eventTable.t_stamp < DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-60),0)) and code <260 and Line= 'B2'												
					ORDER BY t_stamp DESC), '2021-01-01') AND 
				t_stamp <= coalesce((
					SELECT TOP 1 t_stamp FROM eventTable
					WHERE eventTable.t_stamp > DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) and code <260 and Line='B2'						
					ORDER BY eventTable.t_stamp), dateadd(hour,5, DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0)) )) 				
	) sub1	
	where t_stamp <= DATEADD(hh,7,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))
)sub2 
where sub2.code=250 	
group by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd'), sub2.Line
order by format(dateadd(hh,-8, t_stamp), 'yyyy-MMM-dd') desc

```

[Next page](https://forum.inductiveautomation.com/t/query-help-sql-date-range-between-grouping-7am-to-7am/59315.md?page=2)