CODEWITHSUNDEEP
mysqlsqldatetime
Julien
Julien

Reputation: 4163

select dates on this weekday in the past

I have a list of dates ("start", datetime) and I would like to select all dates where :

today = start + 1 WEEK or 
today = start + 2 WEEK or 
today = start + 3 WEEK or 
today = start + 4 WEEK or 
today = start + 5 WEEK or 
today = start + 6 WEEK

Maximum is start + 6 weeks.

Any idea ?

Upvotes: 0

Views: 131

Answers (4)

amdixon
amdixon

Reputation: 3833

setup

create table example
(
  id integer primary key not null auto_increment,
  start datetime not null
);

insert into example ( start )
values
( date_sub(current_date, interval 1 week) ),
( date_sub(current_date, interval 2 week) ),
( date_sub(current_date, interval 3 week) ),
( date_sub(current_date, interval 4 week) ),
( date_sub(current_date, interval 5 week) ),
( date_sub(current_date, interval 6 week) ),
( date_sub(current_date, interval 6 week) ),
( date_sub(current_date, interval 4 week) ),
( date_sub(current_date, interval 9 week) ),
( date_sub(current_date, interval 12 week) )
;

query

select id, start
from example
where 
date(start) in
(
 date_sub(current_date, interval 1 week) ,
 date_sub(current_date, interval 2 week) ,
 date_sub(current_date, interval 3 week) ,
 date_sub(current_date, interval 4 week) ,
 date_sub(current_date, interval 5 week) ,
 date_sub(current_date, interval 6 week)
)
;

output

+----+-----------------------------+
| id |            start            |
+----+-----------------------------+
|  1 | September, 16 2015 00:00:00 |
|  2 | September, 09 2015 00:00:00 |
|  3 | September, 02 2015 00:00:00 |
|  4 | August, 26 2015 00:00:00    |
|  5 | August, 19 2015 00:00:00    |
|  6 | August, 12 2015 00:00:00    |
|  7 | August, 12 2015 00:00:00    |
|  8 | August, 26 2015 00:00:00    |
+----+-----------------------------+

sqlfiddle

Upvotes: 2

O. Jones
O. Jones

Reputation: 108641

I assume you want a WHERE filter to capture all rows containing start DATETIMEs on this weekday one week ago, and two ... six weeks ago. That's the effect of the logic in your question:

today = start + 1 WEEK or  today = start + 2 WEEK or ...

means the same thing as 

start = today - 1 WEEK  etc.

The thing is, you are using DATETIME values for start. They're not guaranteed to be start = CURDATE() because they may not be at midnight.

So, you need to use the DATE() function to reduce them to midnight values before comparing them. Something like this will work.

 WHERE DATE(start) IN (
    CURDATE() - INTERVAL 6 WEEK, CURDATE() - INTERVAL 5 WEEK, CURDATE() - INTERVAL 4 WEEK,
    CURDATE() - INTERVAL 3 WEEK, CURDATE() - INTERVAL 2 WEEK, CURDATE() - INTERVAL 1 WEEK)

You could also do this -- it picks out all records six weeks old or newer, but not the ones in the most recent week, then picks the ones on today's weekday.

 WHERE start >= CURDATE() - INTERVAL 6 WEEK
   AND start <  CURDATE() - 6 DAY
   AND WEEKDAY(CURDATE()) = WEEKDAY(start)

This second formulation will be more efficient if you have a great deal of old data in your table and you have an index on your start column: the first two where clauses are sargeable.

Pro tip: When specifying this kind of date filter, the more effort you spend making your specification exact before you write code, the faster you will finish your work. That's true even if you don't count debugging time.

Upvotes: 1

srikanth r
srikanth r

Reputation: 332

Try To run this query in mysql,

use dbname;
create table adddate
(
  id integer primary key not null auto_increment,
  initial datetime not null
)auto_increment=100;

insert into adddate ( initial )
values( DATE_ADD(current_date, interval 1 week) ),
( DATE_ADD(current_date, interval 2 week) ),
( DATE_ADD(current_date, interval 3 week) ),
( DATE_ADD(current_date, interval 4 week) ),
( DATE_ADD(current_date, interval 5 week) ),
( DATE_ADD(current_date, interval 6 week) )
;

date_add is an SQLfunction it is used to do addition operations on date

The syntax is:-

DATE_ADD(date,INTERVAL expr type)

the type can be:-

MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH;

Upvotes: 0

Blind Trevor
Blind Trevor

Reputation: 746

Could you use between?

So...

today BETWEEN start AND start + INTERVAL 6 WEEK

Something like that?

Upvotes: 0

Related Questions