Data Group By Hour, Fill In Missing Hours

Ignition
, ,
1

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.

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)

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
2

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. :wink:

# 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)
3

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!! :slight_smile: It works fine…

Thanks again for your help…

1 Like