how to query current week and month data with `SQL` in MySQL?

Question

Supposed I have data as below:

id	cnt	insert_time
1	234	1679131273
2	12	1679131273
3	80	1679131514
4	30	1679131514
5	124	1679131605
6	4.3	1679131605
7	4	1679131935
8	1.5	1679131935
9	5	1679292635
10	56	1679292635

I can use the below SQL to get data of weeks, but I don't need a list(and the data of week's first day is Sunday, not Monday), I just need the current week, and it's from Monday to now, how to query current week and month data with SQL in MySQL?(week data from monday, and month data from first day of the month)

SELECT CONCAT(YEAR(FROM_UNIXTIME(created_at,'%Y-%m-%d')), '/', WEEK(FROM_UNIXTIME(created_at,'%Y-%m-%d'))) as weeks
,id
,sum(cnt) as total_cnt
FROM project
GROUP BY weeks,uid
ORDER BY weeks desc,total_cnt desc

Thanks so much for any advice.

Answer 1

How to query current week and month data with SQL (week data from monday, and month data from first day of the month)?

If you want the sum of all counts for the current month, you can use a where clause:

select sum(cnt) as sum_cnt
from project
where created_at >= unix_timestamp(date_format(current_date, '%Y-%m-01'))

Rationale :

• current_date returns the current system date
• date_format(..., '%Y-%m-01') truncates to the first day of the month
• unix_timestamp turns the results to a unix timestamp, that you can use to filter your column

---

If you wanted the current week:

select sum(cnt) as sum_cnt
from project
where created_at >= unix_timestamp(current_date - interval weekday(current_date) day)

weekday(current_date) returns the index of the current date (0 for Monday, 1 for Tuesday, ...), which we can use to offset the date to Monday.

Related reading: MySQL Date and Time functions.