# How to group by the hour portion of a t\_stamp

**URL:** <https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395>\
**Category:** General Discussion\
**Created:** [April 25, 2019, 3:59pm UTC](https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395 "2019-04-25T15:59:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ddarby48](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@ddarby48](https://forum.inductiveautomation.com/u/ddarby48)\
**Post date:** [April 25, 2019, 3:59pm UTC](https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395/1 "2019-04-25T15:59:44Z")

</div>

I have data displayed in a table that is collected every minute. I would like to sum the data, easy enough, and group by the hour portion of the t\_stamp. How do I script the group by portion?

---

<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:** [April 25, 2019, 4:54pm UTC](https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395/2 "2019-04-25T16:54:39Z")

</div>

You didn’t mention what database you’re using, but in Postgresql, it would be something like:

```sql
SELECT to_char(t_stamp, 'YYYY-MM-DD HH24') as t_date, count(*)
FROM table
GROUP BY t_date

```

Note that I also used the date in the format string to keep dates separated. 🙂

---

<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:** [April 25, 2019, 5:18pm UTC](https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395/3 "2019-04-25T17:18:42Z")

</div>

If you don’t need to display the raw data, have your DB do the grouping and summation for you. If you want to group and sum without another DB round-trip, you might find the [view()](https://www.automation-pros.com/simaids/doc/expression.view.html) function from my [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) module helpful. Place something like the following as an expression binding on the 2nd table’s data property:

```auto
view("SELECT system.date.fromMillis(t_stamp[0].time - (t_stamp[0].time % 36000000)) As hour, "
+ "sum(someColumn) As sumOfColumn "
+ "GROUP BY t_stamp.time - (t_stamp.time % 36000000) "
+ "ORDER BY hour", {Root Container.Table.data})

```

---

<div class="post-metadata">

**Author:** ![ddarby48](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@ddarby48](https://forum.inductiveautomation.com/u/ddarby48)\
**Post date:** [April 25, 2019, 5:31pm UTC](https://forum.inductiveautomation.com/t/how-to-group-by-the-hour-portion-of-a-t-stamp/24395/4 "2019-04-25T17:31:13Z")

</div>

I got it working by using this command:

group by DATEPART(hh, t\_stamp)  
order by t\_stamp asc  
Thanks for the advice, it is greatly appreciated.