Using BETWEEN in a where clause with additional args

Databases
1

I’m new to using BETWEEN in a WHERE clause, so hopefully someone more experienced can tell me if the following is correct:


UPDATE users SET account_status = 1 WHERE user_created_at BETWEEN :begin_date AND :end_date AND id_user > 3000.

What I want is for the query to set a user’s account status to 1 if the user_created_at is between two different dates, and the id of the user is above 3000. But there are a lot of ANDs here. I guess mysql will recognize the first AND after a BETWEEN statement to point toward the first arg of BETWEEN and the second arg of BETWEEN. Or are additional ANDs factored into the BETWEEN, or are they just added to the WHERE clause in general? I tried the above query and it appeared to work, but I want to avoid side effects later on due to a lack of understanding of BETWEEN and adding additional args to the WHERE clause.

2

Exactly.

For the human eye, though, it’s easier to understand a complicated query if you don’t write everything on one single line:


UPDATE users 
SET account_status = 1 
WHERE user_created_at BETWEEN :begin_date AND :end_date 
AND   id_user > 3000
3

this could give unexpected results if user_created_at is DATETIME or TIMESTAMP, and :begin_date and :end_date are passed in as date string

consider getting all the rows for the first three days of july –

WHERE user_created_at BETWEEN '2014-07-01' AND '2014-07-03'

if user_created_at is a DATETIME value, you won’t get any rows for July 3rd

4

Good point on the formatting and the example.

@r937. Thanks for the heads up. I use unix time here though, so it’s not a problem.