Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL: How can I find 'last monday's date' (performance Issue)

Tags:

date

mysql

is there a simpler way than writing:

select date_sub(curdate(), interval WEEKDAY(curdate()) day) as LastMonday
from dual

like image 328
lexu Avatar asked Aug 13 '09 08:08

lexu


People also ask

How can I get yesterday date record in MySQL?

In MySQL, you can subtract any date interval using the DATE_SUB() function. Here, since you need to subtract one day, you use DATE_SUB(CURDATE(), INTERVAL 1 DAY) to get yesterday's date.

How can I get specific date records in MySQL?

You can use DATE() from MySQL to select records with a particular date. The syntax is as follows. SELECT *from yourTableName WHERE DATE(yourDateColumnName)='anyDate'; To understand the above syntax, let us first create a table.

Where is weekday in MySQL?

MySQL WEEKDAY() FunctionThe WEEKDAY() function returns the weekday number for a given date. Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.


1 Answers

If you're not using an ancient MySQL, you can wrap this in a stored function.

CREATE FUNCTION `LastMonday`() RETURNS DATETIME    
  RETURN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ;

and then call

select LastMonday() as LastMonday

Update:

If you're having performance problems, you can persist the value in a session variable. That way you can be sure that it will only be calculated once.

set @LastMonday=LastMonday();
select @Lastmonday; 

(in this simple query it makes no difference of course...)

like image 125
Wouter van Nifterick Avatar answered Sep 22 '22 14:09

Wouter van Nifterick