select dates on this weekday in the past

Question

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 ?

where today in (start + INTERVAL 1 WEEK, start + INTERVAL 2 WEEK, ...)? — jarlh, Sep 23 '15 at 09:20
You're using `DATETIME` items for your `start` column. Can these items contain dates *and* times? Or are they all set to midnight on the date in question? When you say "I would like to select all dates where ..." do you mean "I would like to select all rows having dates where...." ? — O. Jones, Sep 26 '15 at 11:17

amdixon · Answer 1 · 2016-01-03T04:04:54.780

2

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

edited Jan 03 '16 at 04:04

answered Sep 23 '15 at 09:28

amdixon

3,814
8
25
34

thank you but it's not start in the current_date up to 6 weeks, but the opposite, as @jarlh wrote, no? – Julien Sep 23 '15 at 09:56
from the specification.. today == September, 16 + 1 week, today == August 26 + 4 weeks, etc. if you want different output, specify the difference – amdixon Sep 23 '15 at 09:58
technically i wrote today - 1 week, today - 2week.. in the sql. but check if the output conforms to the specification – amdixon Sep 23 '15 at 10:07
my mistake, I didn't see this substract – Julien Sep 23 '15 at 10:09
The above one is correct just change date_sub to date_add – srikanth r Sep 23 '15 at 10:33
`today = start + 1 WEEK` <=> `start = today - 1 WEEK`. check the specification and the output – amdixon Sep 23 '15 at 10:36
Careful: the `start` values might not be at midnight. – O. Jones Sep 26 '15 at 11:43
@OllieJones as suggested, modified to truncate the timestamp portion before comparison – amdixon Sep 26 '15 at 11:56

score 1 · Accepted Answer · edited May 23 '17 at 11:44

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.

score 0 · Answer 3 · answered Sep 23 '15 at 09:17

0

Could you use between?

So...

today BETWEEN start AND start + INTERVAL 6 WEEK

Something like that?

answered Sep 23 '15 at 09:17

Blind Trevor

746
2
9
28

Ah - you've somewhat expanded on the question since I answered! – Blind Trevor Sep 23 '15 at 10:53

srikanth r · Answer 4 · 2015-09-23T10:36:55.547

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;

select dates on this weekday in the past

4 Answers4