# Creating a dataset by hour for Box and whisker plot

**URL:** <https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682>\
**Category:** Ignition\
**Tags:** ignition79, ignition80\
**Created:** [February 22, 2021, 3:53pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682 "2021-02-22T15:53:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![nickboratyn](https://avatars.discourse-cdn.com/v4/letter/n/9d8465/32.png) [@nickboratyn](https://forum.inductiveautomation.com/u/nickboratyn)\
**Post date:** [February 22, 2021, 3:53pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/1 "2021-02-22T15:53:12Z")

</div>

Hello all, I am working in ignition 8.1 with the vision module.  
I have a dataset that consists of the last 24 hours worth of data for a single temp tag. So the current dataset is two columns: a time column and the column for all the values.  
I want to break up or create a new dataset from that current dataset that will have each hour of the 24 hours in column form so I can them bind that dataset to a box and whisker plot to show each hour on operation throughout the day.  
This is the current dataset that I have:

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

Thank you for any help

---

<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:** [February 22, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/2 "2021-02-22T16:50:38Z")

</div>

Sounds like a job for the [view()](https://www.automation-pros.com/simaids/doc/expression.view.html) expression function from my (free) [Simulation Aids module](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids). Grouping by hour and pivoting on the hour.

---

<div class="post-metadata">

**Author:** ![nickboratyn](https://avatars.discourse-cdn.com/v4/letter/n/9d8465/32.png) [@nickboratyn](https://forum.inductiveautomation.com/u/nickboratyn)\
**Post date:** [February 22, 2021, 6:55pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/3 "2021-02-22T18:55:43Z")

</div>

is there anyway to do what I want without installing your module?

---

<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:** [February 22, 2021, 7:39pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/4 "2021-02-22T19:39:44Z")

</div>

Sure. Write jython to process the data. As noted in the documentation, if you get something working with the view() function but need to deploy without it, its debugger can be turned on to show you the jython it used for your case. You could then re-deploy that in a script module and call from a property change event.

---

<div class="post-metadata">

**Author:** ![Carolina\_Elfeky2](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@Carolina\_Elfeky2](https://forum.inductiveautomation.com/u/Carolina_Elfeky2)\
**Post date:** [June 21, 2023, 2:50pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/5 "2023-06-21T14:50:11Z")

</div>

Hey, Phil,

I'm having trouble grouping by hour.

This simple statement works  
select system.date.getHour24(t\_stamp) as hour24, system.aggregate.count(high\_cap)

but when I add the group by clause,

select system.date.getHour24(t\_stamp) as hour24, system.aggregate.count(high\_cap)  
Group By system.date.getHour24(t\_stamp)

I get the following error

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

---

<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:** [June 21, 2023, 2:54pm UTC](https://forum.inductiveautomation.com/t/creating-a-dataset-by-hour-for-box-and-whisker-plot/43682/6 "2023-06-21T14:54:34Z")

</div>

When grouping, in the SELECT expressions, all of the columns are delivered as python lists. When you use a bare column name, my parser will stick a `[0]` onto the select expression for you, but once you wrap in some other function, you will have to handle it yourself (if the function doesn't take lists).

So, you should use:

```auto
SELECT system.date.getHour24(t_stamp[0]) As hour24, system.aggregate.count(high_cap)
GROUP BY system.date.getHour24(t_stamp)

```