Back to list PostgreSQL How to Group by Month in PostgreSQL Database: PostgreSQL Operators:DATE_TRUNC(), GROUP BY, COUNT() Problem: You'd like to group records by month in a PostgreSQL database. Example: Our database has a table named watch with data in the columns id, name, and production_timestamp. idnameproduction_timestamp 1watch2019-03-01 11:45:23 2smartwatch2019-09-15 07:35:13 3smartband2019-09-22 17:22:05 Solution: You can use the DATE_TRUNC() function to group records in a table by month. Here's the query you would write: SELECT DATE_TRUNC('month',production_timestamp) AS production_to_month, COUNT(id) AS count FROM watch GROUP BY DATE_TRUNC('month',production_timestamp); Here's the result of the query: production_to_monthcount 2019-03-01 00:00:001 2019-09-01 00:00:002 Discussion: Use the DATE_TRUNC() function if you want to retrieve a date or time with a specific precision from a PostgreSQL database. (In our example, we used month precision.) This function takes two arguments. First, we have the date part specifier (in our example, 'month'). Note that the specifier is a string and needs to be enclosed in quotes. The second argument is the timestamp value; this can be an expression returning a timestamp value or the name of a timestamp column. (In our example, we use the column production_timestamp). The function DATE_TRUNC() truncates the timestamp to the given precision (in this case, the month). In our database, the smartwatch has the production date 2019-09-15 07:35:13; after it's been truncated to month precision, it becomes 2019-09-01 00:00:00. The month-level precision causes the day to be displayed as '01' and the time to be filled with zeros. (The truncated date parts (e.g. days, months) of the timestamp are replaced with ones.) Grouping records by month is very common in PostgreSQL. In our example, the number of products is totaled for each month. (The COUNT() aggregate function counts the number of products). If you group records using an aggregate function, you have to use the GROUP BY clause. After the GROUP BY clause, you should place the columns or expressions used with the aggregate function in the SELECT statement. (In our example, this is DATE_TRUNC( 'month', production_timestamp).) Of course, you don't have to use the DATE_TRUNC() function just to group records. You can also use it to get the first day of the month for a given date. For each watch, let's get the name and production date to month precision – no grouping needed. Here's the query you would write: SELECT name, DATE_TRUNC('month',production_timestamp) AS production_to_month FROM watch; Here's the result: nameproduction_to_month watch2019-03-01 00:00:00 smartwatch2019-09-01 00:00:00 smartband2019-09-01 00:00:00 In this query, the function DATE_TRUNC() truncates the timestamp to month precision. In our database, the smartwatch has the production date '2019-09-15 07:35:13'; now it's '2019-09-01 00:00:00', which is the first day of the month. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Extract the Week Number from a Date in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.