You can GROUP BY
the year and the month from a SQLite date by using the strftime()
function with the %Y
and %m
format specifiers like so:
strftime('%Y-%m', date_col)
For example, consider the following table of blog posts:
+----+-----------------+---------------------+
| id | title | date_posted |
+----+-----------------+---------------------+
| 1 | Lorem Ipsum | 2020-01-01 00:00:00 |
| 2 | Proin ut Ante | 2021-08-01 00:00:00 |
| 3 | Curabitur nulla | 2021-08-01 00:00:00 |
| 4 | Fusce vitae | 2015-12-01 00:00:00 |
+----+-----------------+---------------------+
To GROUP BY
only the year and the month from the date_posted
column, you would use strftime()
in the following way:
SELECT COUNT(*) total_posts, strftime('%Y-%m', date_posted) year_month
FROM blog_post
GROUP BY year_month
ORDER BY year_month DESC
This would result in the following:
+-------------+------------+
| total_posts | year_month |
+-------------+------------+
| 2 | 2021-08 |
| 1 | 2020-01 |
| 1 | 2015-12 |
+-------------+------------+
If you would like to try this example yourself, then you can create the table and insert the data (as shown in the examples above), in the following way:
CREATE TABLE `blog_post` (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(25) NOT NULL,
date_posted DATETIME NOT NULL
);
INSERT INTO blog_post(`title`, `date_posted`) VALUES
('Lorem Ipsum', '2020-01-01 00:00:00'),
('Proin ut Ante', '2021-08-01 00:00:00'),
('Curabitur nulla', '2021-08-01 00:00:00'),
('Fusce vitae', '2015-12-01 00:00:00');
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.