mysql group by week , one month and three recent month-CodePudding

I have a table with column named started_at
I want to get statistics of new inserted row by last day , week , one month and three month .
the started_at column format is default MySQL timestamp which is string . before posting this question , I try this querys

SELECT WEEK(`started_at`) , COUNT(*) AS nbr FROM users_in_bots WHERE `bot_id` = 5529 GROUP BY WEEK (`started_at`);
SELECT MONTH(`started_at`), COUNT(*) AS nbr FROM users_in_bots WHERE `bot_id` = 5529 GROUP BY MONTH(`started_at`);

and the result is not what I want . I want get all statistics with just one query .

the table structure :

CREATE TABLE `users_in_bots` (
  `user_id` bigint(20) NOT NULL,
  `bot_id` bigint(20) NOT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

sample row :

INSERT INTO `users_in_bots` (`user_id`, `bot_id`, `started_at`) VALUES
(2314, 509492849, '2022-02-27 03:59:21'),
(28779, 210686266, '2022-03-03 21:51:38'),
(28779, 503513058, '2022-04-01 12:28:37'),
(28779, 515774720, '2022-03-25 08:25:16'),
(28779, 518099352, '2022-03-22 17:22:38'),
(28779, 519646468, '2022-03-04 22:02:02'),
(84588, 517141146, '2022-03-28 12:36:45'),
(87075, 509498849, '2022-02-27 03:59:21'),
(116264, 210509102, '2022-02-27 00:02:54'),
(116264, 212268136, '2022-02-27 00:29:06');

expected output ( what i wish to use in my application ):
new users in last 24 hour : 42
new users in last week : 532
new users in last month : 4568
and same with 3 and six month and all the time .

CodePudding user response：

You can use conditional aggregation to get the results you want. For example:

SELECT SUM(started_at BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()) last_hour,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE()) last_week,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()) last_month,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 3 MONTH AND CURDATE()) last_3month,
       SUM(started_at BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE()) last_6month
FROM users_in_bots

Output (for your sample data as of 2022-04-13):

last_hour   last_week   last_month  last_3month last_6month
0           0           4           10          10

Demo on dbfiddle