How to group by the hour portion of a t_stamp

General Discussion
1

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?

2

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

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

3

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() function from my Simulation Aids module helpful. Place something like the following as an expression binding on the 2nd table’s data property:

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})
4

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.