How to Group by Month in SQLite

Problem

You'd like to group records by month in an SQLite database.

Example

Our database has a table named notebooks with data in the columns id, color, and production_timestamp.

Solution

You can use the STRFTIME() function to get the month and year value from the date. You can also use it to group these values.

Here's the query you would write:

SELECT
  STRFTIME('%m-%Y', production_timestamp) AS production_month,
  COUNT(id) AS count
FROM notebooks
GROUP BY 
  STRFTIME('%m-%Y', production_timestamp);

Here’s the result of the query:

Discussion

Grouping records by month is a very common operation in SQLite. In our example, the number of notebooks produced is totaled for each month. You usually want to take into account both the year and the month, i.e. you want to group notebooks produced in January 2022 separately from notebooks produced in January 2021.

We use the STRFTIME() function to get the month and year from the date. This function returns the date formatted according to the format string specified as the first argument. Here, we use the '%m-%Y' format: %m stands for the numeric month (01 through12), and %Y stands for the 4-digit year. The second argument is the date (here, the production_timestamp column). In our database, the Orange notebook has the production date 2022-01-22 17:22:05; after it's been truncated to month precision, it becomes 01-2022.

We use STRFTIME() in the GROUP BY clause to group by the year and month and in the SELECT clause to display the label for each month.