CODEWITHSUNDEEP
mysqlsql
marco masx
marco masx

Reputation: 15

How DO I fetch last Week and current week

How do I fetch last week data from monday time (00:00:01) and end on sunday time (23:59:59)... same as this current week from monday time (00:00:01) and end on sunday time (23:59:59)

WHat I tried!

$query = "SELECT users.name,count(*) as count, 
                 campaign.campaign_name, 
                 campaign.payout_cost*count(*) as totalPrice
            FROM users 
            JOIN transactions on users.uid=transactions.uid 
            JOIN campaign on campaign.campaign_id_id=transactions.campaign_id 
           WHERE uid=$uid 
             AND `date` >= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6 DAY)   
             AND `date` <  DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY) 
           GROUP BY campaign.campaign_name_name ";

Upvotes: 1

Views: 117

Answers (3)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

You can use WEEK() function, which returns the week number for a given date, by adding

AND WEEK(date-INTERVAL 1 DAY) = WEEK(NOW()) - 1 to get current week's data starting from monday upto sunday,

and

AND WEEK(date-INTERVAL 1 DAY) = WEEK(NOW()) - 2 for the previous week's data

into the WHERE condition after WHERE uid=$uid

such as

$query = "SELECT c.campaign_name,
                 COUNT(*) as total_count,
                 SUM(c.payout_cost) as total_payout
            FROM transactions t
            JOIN campaign c
              ON c.campaign_id = t.campaign_id
           WHERE uid = $uid
             AND WEEK(date - INTERVAL 1 DAY) = WEEK(NOW()) - 1               
           GROUP BY c.campaign_name ";

and replace WEEK(NOW()) - 1 with WEEK(NOW()) - 2, also

Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You are on the right track by avoiding functions like week() on the column -- that just messes up the optimizer. On the other hand, the uid parameter should be passed as a parameter rather than munging the query string.

You want to use the weekday() function because you want weeks to start on a Monday. Just some arcaneness of MySQL: weekday() returns 0 for Monday whereas dayofweek() returns 2 for Monday.

So, the logic for the current week would be:

date >= curdate() - interval weekday(curdate()) day and
date < curdate() + interval 7 - weekday(curdate()) day

For last week, this would be:

date >= curdate() - interval 7 + weekday(curdate()) day and
date < curdate() + interval - weekday(curdate()) day

Notes that curdate() (or current_date) returns the current date with no time component, so no date() is required.

Upvotes: 1

switch
switch

Reputation: 116

Couple of ways to do it...

select data from tableName
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

select data FROM tableName
wherdate >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

Upvotes: 0

Related Questions