Question 20396 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    I think this will provide the answer you are looking for:

    SELECT
    uid,
    SUM(IF(insert_time >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL WEEKDAY(CURRENT_DATE) DAY), cnt, 0)) AS cnt_this_week,
    SUM(IF(insert_time >= UNIX_TIMESTAMP(DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')), cnt, 0)) AS cnt_this_month
FROM project
/* compare insert_time with LEAST() of "first of this month" and "Monday of this week" */
WHERE insert_time
    BETWEEN UNIX_TIMESTAMP(LEAST(
        DATE_FORMAT(CURRENT_DATE, '%Y-%m-01'),
        CURRENT_DATE - INTERVAL WEEKDAY(CURRENT_DATE) DAY
    ))
    AND UNIX_TIMESTAMP()
GROUP BY uid;

    I am not using WEEK() in this query but thought it useful to reference this as you mentioned it in your question.

    WEEK(date[,mode])

    This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

    WEEKEDAY(date)

    Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

    You need to be aware of implicit conversion between session timezone and UTC when using FROM_UNIXTIME() and UNIX_TIMESTAMP().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search