# Data Group By Hour, Fill In Missing Hours

**URL:** <https://forum.inductiveautomation.com/t/data-group-by-hour-fill-in-missing-hours/35214>\
**Category:** Ignition\
**Tags:** database, ignition80, scripting\
**Created:** [June 1, 2020, 3:42pm UTC](https://forum.inductiveautomation.com/t/data-group-by-hour-fill-in-missing-hours/35214 "2020-06-01T15:42:48Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [June 1, 2020, 3:42pm UTC](https://forum.inductiveautomation.com/t/data-group-by-hour-fill-in-missing-hours/35214/1 "2020-06-01T15:42:48Z")

</div>

I have a SQL table that records downtime. I have a query that pulls duration in seconds and groups by hours. I then use this for a bar graph. If I want to “fill-in” the missing hours with a result of 0, what would be the best solution, a SQL query, or code in Ignition then bind?

Any tips or hints is much appreciated.

```auto
SELECT 
	DATE_FORMAT(DATE_SUB(a.dtStart, INTERVAL SECOND(a.dtStart) SECOND), "%m-%d %h:00") AS t_stamp,
	SUM(Duration) as Duration
FROM

(SELECT b.t_stamp AS dtStart,
	   a.t_stamp AS dtEnd,
	   TIMESTAMPDIFF(SECOND, b.t_stamp, a.t_stamp) AS duration
   FROM
     (SELECT * ,
             LAG(Id) OVER (ORDER BY t_stamp) AS prev
      FROM MyTable) a
INNER JOIN MyTable b ON a.prev = b.Id
WHERE a.Value = 0 AND b.Value = 1 AND b.t_stamp BETWEEN '2020-04-18 09:59:03' AND '2020-05-27 13:33:09' ) a

group by YEAR(dtStart), MONTH(dtStart), DAY(dtStart), HOUR(dtStart)

```

```auto
t_stamp Duration
04-18 09:00	25
04-18 10:00	33
04-18 01:00	185
04-18 02:00	91
04-18 03:00	44
04-18 04:00	672
04-18 05:00	28
04-18 06:00	479
04-18 07:00	236
04-18 11:00	306

```

---

<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:** [June 1, 2020, 7:06pm UTC](https://forum.inductiveautomation.com/t/data-group-by-hour-fill-in-missing-hours/35214/2 "2020-06-01T19:06:48Z")

</div>

Usually, such things are done by joining with a table that has all the hours in it. Since this needs to be more dynamic, I recommend using a script.

Tested as well as I could, with no query data. 😉

```python
# Set dates. Normally, things would already come in as java.util.Date
d1 = system.date.parse('2020-04-18 09:59:03') 
d2 = system.date.parse('2020-05-27 13:33:09')

# Set query string. Note use of question marks as dates for later substututions.
query = '''SELECT 
	DATE_FORMAT(DATE_SUB(a.dtStart, INTERVAL SECOND(a.dtStart) SECOND), "%m-%d %h:00") AS t_stamp,
	SUM(Duration) as Duration
FROM

(SELECT b.t_stamp AS dtStart,
	   a.t_stamp AS dtEnd,
	   TIMESTAMPDIFF(SECOND, b.t_stamp, a.t_stamp) AS duration
   FROM
     (SELECT * ,
             LAG(Id) OVER (ORDER BY t_stamp) AS prev
      FROM MyTable) a
INNER JOIN MyTable b ON a.prev = b.Id
WHERE a.Value = 0 AND b.Value = 1 AND b.t_stamp BETWEEN ? AND ? ) a

group by YEAR(dtStart), MONTH(dtStart), DAY(dtStart), HOUR(dtStart)
'''

# Query database
result = system.db.runPrepQuery(query, [d1, d2], 'dbConnection')

# Create a dictionary with all possible t_stamp values.
### Make copies of the input dates
h1 = d1.clone() 
h2 = d2.clone()

### Set minutes and seconds to zero
h1.setMinutes(0)
h1.setSeconds(0)
h2.setMinutes(0)
h2.setSeconds(0)
### Add 1 hour to h2. This is necessary for use on the hoursBetween() function used next
### This ensures we have our final possible t_stamp value 
h2 = system.date.addHours(h2,1)

### Create the dictionary
histogramDict = {system.date.format(system.date.addHours(h1, x), 'MM-dd HH:00'):0 for x in range(system.date.hoursBetween(h1,h2))}

# Merge values from the query 
for row in result:
	histogramDict[str(row['t_stamp'])] = row['Duration']
	
# Finally, create a dataset from the merged data 
headers = ['t_stamp', 'Duration'] 
data = [[key, histogramDict[key]] for key in sorted(histogramDict.keys())]
datasetOut = system.dataset.toDataSet(headers, data)

```

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [June 1, 2020, 8:37pm UTC](https://forum.inductiveautomation.com/t/data-group-by-hour-fill-in-missing-hours/35214/3 "2020-06-01T20:37:43Z")

</div>

Awesome!! Thanks for the help.

Any idea why the last row is always 0? I verified the data is correct from the query, but when merging data the very last row never gets the actual data written over the 0.

Edit, nevermind, I’m an idiot!! 🙂 It works fine…

Thanks again for your help…