PostgreSQL

Postgres group by hour with time

A Postgres group by clause is used to divide the rows obtained from the select statement into the groups. By using a GROUP By clause, we can short-list the data by making them appear in the table at once. This clause always contains a column name. Postgres group by the hour with time deals with grouping the data by depending on the hour of timestamp.

Syntax

SELECT

column1,

Function (column2)

FROM

Name_of_table

GROUP BY

Column1;

We can also use more than one column in the command.

GROUP BY CLAUSE Implementation

To explain the concept of a group by clause, consider the below table, named client. This relation is created to contain the salaries of each client.

>> select * from client;

We will apply a group by clause using a single column ‘salary’. One thing I should mention here is that the column which we use in the select statement must be mentioned in the group by clause. Otherwise, it will cause an error, and the command will not be executed.

>> select salary from client GROUP BY salary;

You can see that the resultant table shows that the command has grouped those rows that have the same salary.

Now we have applied that clause on two columns by using a built-in function COUNT() that counts the number of rows applied by the select statement, and then the group by clause is applied to filter the rows by combining the same salary rows. You can see that the two columns that are in the select statement are also used in the group-by clause.

>> Select salary, count (salary) from client group by salary;

Group by hour

Create a table to demonstrate the concept of a group by clause on a Postgres relation. The table named class_time is created with the columns id, subject, and c_period. Both id and the subject have data type variable of integer and varchar, and the third column contain the data type of the TIME built-in feature as we need to apply the group by clause on the table to fetch the hour portion from the whole time statement.

>> create table class_time (id integer, subject varchar(10), c_period TIME);

After the table is created, we will insert data in the rows by using an INSERT statement. In the c_period column, we have added time by using the standard format of time ‘hh:mm: ss’ that must be enclosed in inverted comas. To make the clause GROUP BY working on this relation, we need to enter data so that some rows in the c_period column match each other so that these rows can be grouped easily.

>> insert into class_time (id,subject,c_period) values (2,'Maths','03:06:27'), (3,'English', '11:20:00'), (4,'S.studies', '09:28:55'), (5,'Art', '11:30:00'), (6,'Persian', '00:53:06');

6 rows are inserted. We will view inserted data by using a select statement.

>> select * from class_time;

Example 1

To proceed further in implementing a group by clause by the hour portion of timestamp, we will apply a select command on the table. In this query, a DATE_TRUNC function is used. This is not a user-created function but is already present in Postgres to be used as a built-in function. It will take the ‘hour’ keyword because we are concerned with fetching an hour, and secondly, the c_period column as the parameter. The resultant value from this built-in function by using a SELECT command will be going through the COUNT(*) function. This will count all the resultant rows, and then all the rows will be grouped.

>> Select date_trunc('hour', c_period), count(*) from class_time group by 1;

DATE_TRUNC() function is the truncate function that is applied to the timestamp to truncate the input value into granularity like seconds, minutes, and hours. So, according to the resultant value obtained through the command, two values having the same hours are grouped and counted twice.

One thing should be noted here: the truncate(hour) function only deals with the hour portion. It focuses on the left-most value, regardless of minutes and the seconds used. If the value of the hour is the same in more than one value, the group clause will create a group of them. For instance, 11:20:00 and 11:30:00. Moreover, the column of date_trunc trim the hour portion from the timestamp and display the hour part only while the minute and second is ’00’. Because by doing this, the grouping can only be done.

Example 2

This example deals with using a group by clause along the DATE_TRUNC() function itself. A new column is created to display the resultant rows with the count column that will count the ids, not all the rows. As compared to the last example, the asterisk sign is replaced with the id in the count function.

>> select date_trunc('hour', c_period) AS time_table, COUNT(id) AS count FROM class_time GROUP BY DATE_TRUNC('hour' , c_period);

The resultant values are the same. The trunc function has truncated the hour portion from the time value, and else part is declared as zero. In this way, the grouping by the hour is declared. The postgresql gets the current time from the system on which you have configured the postgresql database.

Example 3

This example does not contain the trunc_DATE() function. Now we will fetch hours from the TIME by using an extract function. EXTRACT() functions work like the TRUNC_DATE in extracting the relevant portion by having the hour and the targeted column as a parameter. This command is different in working and showing results in aspects of providing hours value only. It removes the minutes and seconds portion, unlike to TRUNC_DATE feature. Use the SELECT command to select id and subject with a new column that contains the results of the extract function.

>> Select id, subject, extract (hour from c_period) as hour from class_time;

You can observe that each row is displayed by having the hours of each time in the respective row. Here we have not used the group by clause to elaborate the working of an extract() function.

By adding a GROUP BY clause using 1, we will get the following results.

>> Select extract (hour from c_period) as hour from class_time group by 1;

As we have not used any column in the SELECT command, so only the hour column will be displayed. This will contain the hours in the grouped form now. Both 11 and 9 are displayed once to show the grouped form.

Example 4

This example deals with using two columns in the select statement. One is the c_period, to display the time, and the other is newly created as an hour to show only the hours. The group by clause is also applied to the c_period and the extract function.

>> select _period, extract (hour from c_period) as hour from class_time group by extract (hour from c_period),c_period;

Conclusion

The article ‘Postgres group by hour with time’ contains the basic information regarding the GROUP BY clause. To implement group by clause with hour, we need to use TIME data type in our examples. This article is implemented in Postgresql database psql shell installed on Windows 10.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.