CODEWITHSUNDEEP
mysqlsql
davidahines
davidahines

Reputation: 4094

How do I select two weeks ago in MYSQL?

I have a report that is driven by a sql query that looks like this:

   SELECT batch_log.userid,
         batches.operation_id,
         SUM(TIME_TO_SEC(ramses.batch_log.time_elapsed)),
         SUM(ramses.tasks.estimated_nonrecurring + ramses.tasks.estimated_recurring),
         DATE(start_time)
    FROM batch_log
    JOIN batches ON batch_log.batch_id=batches.id
    JOIN ramses.tasks ON ramses.batch_log.batch_id=ramses.tasks.batch_id
    JOIN protocase.tblusers on ramses.batch_log.userid = protocase.tblusers.userid
   WHERE DATE(ramses.batch_log.start_time) > "2011-02-01" 
     AND ramses.batch_log.time_elapsed > "00:03:00" 
     AND DATE(ramses.batch_log.start_time) < now() 
     AND protocase.tblusers.active = 1
     AND protocase.tblusers.userid NOT in ("ksnow","smanning", "dstapleton")
GROUP BY userid, batches.operation_id, date(start_time)
ORDER BY start_time, userid ASC

Since this is to be compared with the time from the current payperiod it causes an error.
Our pay periods start on a Sunday, the first pay period was 2011-02-01 and our last pay period started the 4th of this month. How do I put that into my where statement to strip the most recent pay period out of the query?

EDIT: So now I'm using date_sub(now(), INTERVAL 2 WEEK) but I really need a particular day of the week(SUNDAY) since it is wednesday it's chopping it off at wednesday.

Upvotes: 21

Views: 37831

Answers (2)

ain
ain

Reputation: 22759

Question isn't quite clear, especially after the edit - it isn't clear now is the "pay period" two weeks long or do you want just last two weeks back from last sunday? I assume that the period is two weeks... then you first need to know how many days the latest period (which you want to ignore, as it isn't over yet) has been going on. To get that number of days you can use expression like

DATEDIFF(today, FirstPeriod) % 14

where FirstPeriod is 2011-02-01. And now you strip that number of days from the current date in the query using date_sub(). The exact expression depends on how the period is defined but you should get the idea...

Upvotes: 2

Reverend Gonzo
Reverend Gonzo

Reputation: 40851

You want to use DATE_SUB, and as an example.

Specifically:

select DATE_SUB(curdate(), INTERVAL 2 WEEK)

gets you two weeks ago. Insert the DATE_SUB ... part into your sql and you're good to go.

Edit per your comment:

Check out DAYOFWEEK:

and you can do something along the lines of:

DATE_SUB(DATE_SUB(curdate(), INTERVAL 2 WEEK), INTERVAL 2 + DAYOFWEEK(curdate()) DAY)

(I don't have a MySql instance to test it on .. but essentially subtract the number of days after Monday.)

Upvotes: 54

Related Questions