mysql: just select something happen in the weekend

I have a table, store 开发者_C百科all user created events, and with a field "start_time" and a "end_time" How to select all events just happens in weekend?

Use DAYOFWEEK():

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

Something like

SELECT * FROM events WHERE DAYOFWEEK(start_time) = 7 
                     OR DAYOFWEEK(start_time) = 1 
                     ...

I would suggest using the BETWEEN operator. Something like:

SELECT ... FROM EVENTS WHERE
    START_TIME BETWEEN :weekend_start AND :weekend_end
    OR END_TIME BETWEEN :weekend_start AND :weekend_end

(where you then figure out the start and end times for the weekend in question, and bind them in appropriately)

The above will find anything that at least overlaps with the weekend. If the event must be entirely contained with the weekend (i.e. can't start before and can't start after) then just change the OR to an AND.