1

Say I want to SELECT all records between two dates plus one record before and one record after that date? All records are ordered by date.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Simon
  • 5,464
  • 6
  • 49
  • 85
  • Is it a date or a datetime? One record before/after a date time could be sub-seconds apart...needs to be made clear here. – AJ. May 06 '11 at 17:32

4 Answers4

6

You could use a union combined with the limit statement. Something like what's below (untested, don't have access to mysql).

(select column from table where datefield > startdate and datefield < stopdate)
union
(select column from table where datefield < startdate order by datefield desc limit 1)
union
(select column from table where datefield > stopdate order by datefield limit 1)

This will give you the next row regardless of where it falls date-wise.

Thanks for syntax fix, ponies.

Tim Coker
  • 6,484
  • 2
  • 31
  • 62
  • 1
    Based on the OPs wording, this seems right, but wonder if it was intentional to **leave out** the start/stop dates, or should the range be >= and <= ??? – AJ. May 06 '11 at 17:31
  • MySQL error 1064 - syntax error, because you can't use LIMIT or ORDER BY in a UNION, without first putting such in brackets/parenthasis for LIMIT/ORDER BY that is to be applied to a specific query in the UNION statement. Also, UNION will remove duplicates - UNION ALL will not, and be faster for it. – OMG Ponies May 06 '11 at 17:43
  • So each statement into parentheses? – Tim Coker May 06 '11 at 17:46
  • ORDER BY should be 'datefield' instead of startdate/stopdate, isn't it? – Simon May 07 '11 at 13:00
3
  (select * from t where date < start_date order by date desc limit 1)
  union (select * FROM t WHERE date between start_date and end_date)
  union (select * from t where date > end_date order by date asc limit 1)
piotrm
  • 12,038
  • 4
  • 31
  • 28
0

You can use functions to add or subtract values, like this:

select * from table where field1 < ADDDATE(  CURTIME() , INTERVAL 1 DAY)

Check this link where there are some examples.

Vismari
  • 745
  • 3
  • 12
-1
SELECT * 
  FROM table 
 WHERE date BETWEEN DATE_ADD(current_date(), INTERAL -1 DAY) 
                AND DATE_ADD(current_date(), INTERVAL 1 DAY);
skynet
  • 9,898
  • 5
  • 43
  • 52
neocanable
  • 5,293
  • 2
  • 23
  • 28
  • +1: Correct, and will use an index if one exists on the `date` column. – OMG Ponies May 06 '11 at 17:32
  • 1
    Wouldn't this include **all** records on prev/next dates? Thought the OP only wanted one record on each end... – AJ. May 06 '11 at 17:33
  • 3
    It doesn't sound like he's asking for the date + 1 day. I read it as he's asking for the records in the date, plus the next record, regardless of whether it's the next day or a year from his end date. – Tim Coker May 06 '11 at 17:34