mysql query of a date, between two date fields

问题:

I'm using MySql 5.5.

I need to find a userid on a date with a particular ip address.

The fields are userid, ipaddress, startdate, enddate.

So for instance I am looking for a userid with ip address 192.168.1.1 on Sep 12 2011.

the query would be similar select * from database where ipaddress='192.168.1.1' and 2011-12-09 is in(startdate and enddate);

Any help to pointing out this logic flaw is welcome. Thank you.

回答1:

It's not very clear if you want:

'2011-12-09' BETWEEN startdate AND enddate

or:

'2011-12-09' = startdate AND '2011-12-09' = enddate

回答2:

the obvious solution would be like:

  [...]
AND
  startdate <= '2011-12-09'
AND
  enddate >= '2011-12-09'

but theres a shortcut using BETWEEN so you can simply write:

  [...]
AND
  '2011-12-09' BETWEEN startdate AND enddate

note: BETWEEN also works for numbers, strings and other stuff, and it's possible to negate it by writing NOT BETWEEN - quite useful sometimes.

回答3:

Since there is a start and end date, maybe something along the line of:

SELECT * FROM table WHERE ipaddress = '192.168.1.1' AND '2011-12-09' >= startdate AND '2001-12-09' <= enddate;

Or as pointed out by ypercube you can use BETWEEN:

SELECT * FROM table WHERE ipaddress = '192.168.1.1' AND '2011-12-09' BETWEEN startdate AND enddate;