# Time series chart with grouped by hour

**URL:** <https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542>\
**Category:** Ignition\
**Created:** [October 21, 2022, 5:58pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542 "2022-10-21T17:58:01Z")\
**Posts on this page:** 9\
**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:** [October 21, 2022, 5:58pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/1 "2022-10-21T17:58:01Z")

</div>

I am getting all the events in my query by grouping on `format(t_stamp, 'yyyy-MM-dd HH')`

However, data is not showing in my time series chart now. So I think I need a datetime conversion.  
What is a good way to overcome this issue efficiently?

Or maybe it is unhappy because I only have t\_stamps when there was an event instead of periodically?

* * *

Tried to fill in the zeroes.  
Something is wrong with my code though.  
I don't see 800 rows at the end.

```auto
today=system.date.now()		
		
#get 800 hours
eightHundredHours = [[system.date.format(system.date.addHours(system.date.addHours(today,-800),x),'yyyy-MM-dd HH:00:00')] for x in range(800)]
		
#get all the dates that are in the query
datesInQuery=[]
for row in range(value.getRowCount()):
		Hour = value.getValueAt(row, 'Hour')
		datesInQuery.append([Hour])
		 
	
#get all the hours in that 800, but not in the query
excludedHours=[x for x in eightHundredHours if x not in datesInQuery]
	
counter=0	
valueHeaders = ['Hour', 'Events']
data = []
for x in range(800):
#set events to 0 if there weren't events during the hour
	if [system.date.format(system.date.addHours(system.date.addHours(today,-800),x),'yyyy-MM-dd HH:00:00')] in excludedHours:
		data.append([str(system.date.format(system.date.addHours(system.date.addHours(today,-800),x),'yyyy-MM-dd HH:00:00')),0])	
	
#get the value if there were events during the hour				
	if [system.date.format(system.date.addHours(system.date.addHours(today,-800),x),'yyyy-MM-dd HH:00:00')] in datesInQuery:
		Hour = value.getValueAt(counter, 'Hour')
		Events = value.getValueAt(counter, 'Events')
		
		data.append([Hour, Events])
		counter=counter+1

data = system.dataset.toDataSet(valueHeaders, data)	
	
return value

```

* * *

Sometimes data shows, and sometimes it just says component error.  
I don't know why.  
I am on 8.1.19 now.

I am having two different issues.  
When I just return value, there are no zeroes:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d1454c0a9a2702b8324544ece608611b17e206c.png)

When I return data, I only get zeroes, though I do get the 800 rows.

From my query:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/2/d20e4fbc7e7874b65733bd50705b954216619ec3.png)

I think it is not comparing the datetimes correctly. Will edit my query.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 21, 2022, 7:03pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/2 "2022-10-21T19:03:28Z")

</div>

> [@zacharyw.larson](#):
>
> grouping on `format(t_stamp, 'yyyy-MM-dd HH')`

Bad.  
You're converting dates to strings and then having to do grouping on strings with all the subsequent problems when the data arrives back into Ignition. There are SQL ways of doing this but they vary by SQL implementation. Search for `SQL DateTime group by date and hour` and your SQL variant.

---

<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:** [October 21, 2022, 7:05pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/3 "2022-10-21T19:05:31Z")

</div>

Thanks  
I think I can use this.

```auto
GROUP BY DATEPART(day, [activity_dt]), DATEPART(hour, [activity_dt]);

```

* * *

It is not right.  
When I group, then t\_stamp is not allowed in select.

---

<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 21, 2022, 7:19pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/4 "2022-10-21T19:19:57Z")

</div>

The canonical form of date truncation for grouping in MS SQL Server is a paired DATEDIFF & DATEADD. Look for it on google. The result column is a timestamp marking the start of the groupable period. Do not compute with dates and times in string form. Ever. Only convert to string to display.

---

<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:** [October 21, 2022, 7:25pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/5 "2022-10-21T19:25:54Z")

</div>

> [@pturmel](#):
>
> DATEDIFF & DATEADD

Thanks  
I think this is helping me a ton.

However, something is messing up when I bind the data.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/0/6065b6c13ee219c1e6bdb944b06e3cec3c276b5c.png)

It isn't showing the hour in 24 hour.

---

<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 21, 2022, 7:30pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/6 "2022-10-21T19:30:17Z")

</div>

> [@zacharyw.larson](#):
>
> It isn't showing the hour in 24 hour.

That's purely a display issue for your table. You might also want an ORDER BY clause matching your GROUP BY clause.

---

<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:** [October 21, 2022, 7:39pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/7 "2022-10-21T19:39:49Z")

</div>

**I wish for this solution.**  
I really hoped there would be a setting for making the timeseries use discrete data instead of interpolating. I think then I could use the query results.  
I would place it in the idea thread, but I don't know if maybe the option exists and I don't know where.

**Trying to append zeroes as an alternative solution.**  
I think my scripting in zeroes might work if I can figure out the python date equivalent of the SQL technique: `dateadd(datediff())`

* * *

From my query testing, after ordering it, looks exactly how I imagine it should.  
Still shows incorrectly when I look at the query results from the binding.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/4/a4773d90ec64afffa89446e72860ef3df7c29de0.png)

* * *

* * *

Found an alternate technique using `.replace`.  
However, I am getting an error  
` AttributeError: 'java.util.Date' object has no attribute 'replace'`

```auto
import datetime

today=system.date.now()	
dt=today
dt = dt.replace(hour=0, minute=0, second=0, microsecond=0)
today = dt

eightHundredHours = [[system.date.format(system.date.addHours(system.date.addHours(today,-800),x),'yyyy-MM-dd HH:00:00')] for x in range(800)]
print eightHundredHours

eightHundredHours = [[system.date.addHours(system.date.addHours(today,-800),x)] for x in range(800)]
print eightHundredHours

```

* * *

```auto
today=system.date.now()	
start=system.date.getDate(1970,1,1)
dt=system.date.hoursBetween( start,today)
dt=system.date.addHours(start,dt)

```

I kind of got the python equivalent of the SQL technique: `dateadd(datediff())`.  
So I will try that Monday. I think the issue is comparing the datetimes.

---

<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:** [October 24, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/8 "2022-10-24T13:41:05Z")

</div>

Problem is that the data is being interpolated instead of discrete.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0bdb4e85a3ceefc99962d8ed224143da1ac24ba7.png)

My solution is not working, which is trying to add zeroes to the hours that had zero events.

Return value is showing the query data like this:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/6/061be3d2074c6ed57d1d5bd386a3bcbf4d7463ce.png)

Return data from my script is showing like this:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/4/d47c626bac1a5eb28d11f6b38ff8e412bd7ffb1f.png)

Script

```auto
	#get a timestamp at the start of this hour
	today=system.date.now()
	start=system.date.getDate(1970,1,1)
	dt=system.date.hoursBetween( start,today)
	dt=system.date.addHours(start,dt)		
		
	#get timesstamps for each hour between 800 hours ago and now
	eightHundredHours = [[system.date.addHours(system.date.addHours(dt,-800),x)] for x in range(800)]
		
	#get all the dates that are in the query
	datesInQuery=[]
	for row in range(value.getRowCount()):
			Hour = value.getValueAt(row, 'Hour')
			datesInQuery.append([Hour])
	
	#get all the hours in that 800, but not in the query
	excludedHours=[x for x in eightHundredHours if x not in datesInQuery]
	
	counter=0	
	valueHeaders = ['Hour', 'Events']
	data = []
	for x in range(800):
	#set events to 0 if zero events during the hour
		if [system.date.addHours(system.date.addHours(dt,-800),x)] in excludedHours:
			data.append([system.date.addHours(system.date.addHours(dt,-800),x),0])	
	
	#get the value if there were events during the hour				
		if [system.date.addHours(system.date.addHours(dt,-800),x)] in datesInQuery:
			Hour = value.getValueAt(counter, 'Hour')
			Events = value.getValueAt(counter, 'Events')
			
			data.append([Hour, Events])
			counter=counter+1

	data = system.dataset.toDataSet(valueHeaders, data)	
	
	return data

```

* * *

I added this to the bottom, and I think I started to find an issue in the script, but I am not sure how to fix it.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/d/0d9eb3149afb818da00ffbfad707ab64a645eb21.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/d/6d8fb6179e2d0f5bc9c1d93ae4b71894fe0436d9.png)  
The excludedHours seem to be a list of lists with one value in them.  
So I am comparing dates to objects.  
edit: Had to edit the images as I had the first two columns order switched.

**I am getting a different date format between the two of them?**

* * *

Still doesn't work and I got them all to appear the same.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/f/8f93204b3864ceff7e61b05e7dd725b64b297e77.png)

```auto
#get a timestamp at the start of this hour
	today=system.date.now()
	start=system.date.getDate(1970,1,1)
	dt=system.date.hoursBetween( start,today)
	dt=system.date.addHours(start,dt)		
		
	#get timesstamps for each hour between 800 hours ago and now
	eightHundredHours = [system.date.addHours(system.date.addHours(dt,-800),x) for x in range(800)]
		
	#get all the dates that are in the query
	datesInQuery=[]
	for row in range(value.getRowCount()):
			Hour = value.getValueAt(row, 'Hour')
			datesInQuery.append(Hour)
	
	#get all the hours in that 800, but not in the query
	excludedHours=[x for x in eightHundredHours if x not in datesInQuery]
	
	counter=0	
	valueHeaders = ['Hour', 'Events']
	data = []
	for x in range(800):
		#set events to 0 if there weren't events during the hour
		if eightHundredHours[x] not in datesInQuery:
			data.append([eightHundredHours[x],0])	
	
		#get the value if there were events during the hour				
		if eightHundredHours[x] in datesInQuery:
			Hour = value.getValueAt(counter, 'Hour')
			Events = value.getValueAt(counter, 'Events')
			data.append([Hour, Events])
			counter=counter+1
			

	data = system.dataset.toDataSet(valueHeaders, data)	
#	tempheader= ['Datesinquery','generatedDates','excluded']
#	testData=[]
#	for row in range(value.getRowCount()):		
# Hour=datesInQuery[row]
# genHour=eightHundredHours[row]
# excludedHour= excludedHours[row]
# testData.append([Hour,genHour,excludedHour])
#	data= system.dataset.toDataSet(tempheader, testData)
	
	return data
	
	

```

* * *

Still the comparing is not working.  
I even revised my code to get all the hours from math on the query hours.

```auto
#get all the hours that are in the query
	hoursInQuery=[]
	for row in range(value.getRowCount()):
		Hour = value.getValueAt(row, 'Hour')
		hoursInQuery.append(Hour)
	
	#get generate hours before the hour at the end of the query
	lastRow=value.getRowCount()-1
	hoursToGen=671	
	generatedHours= [system.date.addHours(system.date.addHours( value.getValueAt(lastRow, 'Hour') ,-hoursToGen),x) for x in range(hoursToGen)]
	
	#get the generated hours not in the query
	exclusiveHours= [x for x in generatedHours if x not in hoursInQuery]
	
	counter=0	
	valueHeaders = ['Hour', 'Events']
	data = []
	for x in range(hoursToGen):
		#set events to 0 if there weren't events during the hour
		if generatedHours[x] in exclusiveHours:
			data.append([generatedHours[x],0])	
	
		#get the value if there were events during the hour				
		if generatedHours[x] not in exclusiveHours:
			Hour = value.getValueAt(counter, 'Hour')
			Events = value.getValueAt(counter, 'Events')
			data.append([Hour, Events])
			counter=counter+1			

	#data = system.dataset.toDataSet(valueHeaders, data)	
	tempheader= ['Datesinquery','generatedDates','excluded']
	testData=[]
	for row in range(value.getRowCount()):			
		Hour=hoursInQuery[row]
		genHour=generatedHours[row]
		excludedHour= exclusiveHours[row]
		testData.append([Hour,genHour,excludedHour])
	data= system.dataset.toDataSet(tempheader, testData)
	
	return data

```

---

<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:** [October 24, 2022, 8:53pm UTC](https://forum.inductiveautomation.com/t/time-series-chart-with-grouped-by-hour/65542/9 "2022-10-24T20:53:32Z")

</div>

Got help from support.  
Was getting a Timestamp from SQL.  
Had to convert that to a string, then parse it to datetime.

Tried a ton of other ways, but finally got it this way.

This line helped me a ton.

```auto
return [type(hoursInQuery[1]),value.getValueAt(lastRow, 'Hour'),type(generatedHours[671]),generatedHours[801],type(exclusiveHours[1])]

```

The component was erroring when the dates were coming in out of order. Had to fix the parsing.  
HH instead of hh for the hours.