When you could have a TIME column in your PostgreSQL desk, you possibly can group the rows primarily based on the hours. You would possibly wish to choose varied columns that match the given standards which are laid out in your GROUP BY clause. PostgreSQL means that you can group by hour out of your column containing a TIME worth.
This publish discusses how the grouping works by presenting varied examples to grasp how the GROUP BY hour works. Let’s start!
Working with the PostgreSQL GROUP BY Hour (Time)
The GROUP BY clause helps completely different capabilities, particularly when working with date/time. Within the case of time, you need to use two major capabilities: the date_func and extract(). You may simply group the rows primarily based on the hour in your time column utilizing the 2 capabilities.
We use the next desk for the completely different examples all through the publish:
Instance 1: GROUP BY Hour
Whenever you wish to group your desk utilizing your timestamp column, you possibly can truncate the metric that you simply want to use for the grouping. The DATE_TRUNC() means that you can group a timestamp column by hour, minute, day, week, and so forth. primarily based on the chosen columns.
You deal with the “hour” because the metric for grouping by hour. The next instance command truncates the timestamp column to group the rows primarily based on the hour:
SELECT DATE_TRUNC (‘hour’, hours_d) FROM particulars GROUP BY DATE_TRUNC (‘hour’, hours_d);
Since no column is chosen, the output returns the group row which incorporates the hours from the timestamp column:
You can implement the command as illustrated within the following if you wish to choose extra columns and provides a greater identify for the grouped rows:
SELECT product, DATE_TRUNC (‘hour’, hours_d) AS grouped_hours FROM particulars GROUP BY product, DATE_TRUNC (‘hour’, hours_d);
Right here, we choose the “product” column and add the “AS” assertion to call the output row for the hours grouping.
Instance 2: GROUP BY Hour Utilizing EXTRACT()
You may get the varied parts of the timestamp column with EXTRACT(). To date, we’ve seen how one can group the rows primarily based on the hour. Nevertheless, it’s potential additionally to extract solely the hour of the day, leaving out the minutes and the seconds from the timestamp column.
Right here’s an instance of extracting the hour with out utilizing the GROUP BY clause:
SELECT EXTRACT (hour FROM hours_d) AS hours FROM particulars;
Instance 3: Utilizing the Mixture Features with the GROUP BY Hour
Mixture capabilities are useful in reaching particular queries when working with the GROUP BY hour clause. As an example, if you wish to depend all of the rows which incorporates the identical hour, you need to use COUNT() as demonstrated within the following picture:
The earlier output exhibits two entries within the desk which include the identical hour. All the opposite values have distinctive hours of their timestamp, and their depend returns as 1.
With the MAX () mixture operate, you possibly can group the hours within the timestamp column by the hour and organize them in descending order. Right here’s an instance:
The output exhibits how the grouped row incorporates the merchandise which are grouped by hour from the newest hour right down to the earliest within the desk. In the event you deal with the hours on a selected day, you would implement the identical instance to see the urgency in an orders_table.
The way you implement the group-by-hour command is determined by the wants of your queries and what you want to obtain.
Conclusion
This publish defined the group-by-hour clause. We defined how you can group the timestamp columns utilizing the EXTRACT and DATE_TRUNC capabilities. Furthermore, we’ve seen how you can use the combination capabilities with the group by hour. Hopefully, now you can work with the PostgreSQL GROUP BY hour (time).
