CODEWITHSUNDEEP
mysqlsql
ATMathew
ATMathew

Reputation: 12856

Using Between on only time portion of dates

I have the following query:

SELECT COUNT(*) FROM leads WHERE create_date > '06:00' AND create_date < '18:00';
SELECT COUNT(*) FROM leads WHERE create_date > '06:00' AND create_date < '18:00';

However, the create_date column looks like '2011-08-26 10:18:01' and i want to find everything between just those time periods, regardless of the day.

How can I just query the time from the create_date values?

How would also find all values not within those time periods? NOT BETWEEN ....

Upvotes: 0

Views: 851

Answers (4)

Michael Mior
Michael Mior

Reputation: 28752

To get the time from the date, you can use the TIME() function.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND create_date < '18:00';
SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND create_date < '18:00';

To find times outside this range, just invert everything in your where clauses.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) < '06:00' AND create_date > '18:00';
SELECT COUNT(*) FROM leads WHERE TIME(create_date) < '06:00' AND create_date > '18:00';

Upvotes: 3

Michael Berkowski
Michael Berkowski

Reputation: 270609

Use the TIME() function to return only the time portion of a DATETIME column.

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND TIME(create_date) < '18:00';

To find the values outside these time periods, use 

SELECT COUNT(*) FROM leads WHERE TIME(create_date) <= '06:00' AND TIME(create_date) >= '18:00';

Upvotes: 2

Dave
Dave

Reputation: 11879

You can just cast to a TIME:

SELECT COUNT(*) FROM leads WHERE TIME(create_date) > '06:00' AND TIME(create_date) < '18:00';

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 50970

Assuming that create_date is of type DATETIME, to extract the time part only use TIME(create_date).

And yes, NOT BETWEEN will get the inverse set.

Upvotes: 2

Related Questions