CODEWITHSUNDEEP
mysqldatabase
Umair
Umair

Reputation: 346

MySQL BETWEEN Query on DATETIME formate

I am reading data from MySQL database. I successfully read data where time is equal to given time by the following query (This returns me valid data from alldata table.)

SELECT *
FROM alldata
WHERE Time = DATE_FORMAT(NOW()- INTERVAL 1 SECOND,'%Y-%m-%d %H:%i:%s')
LIMIT 0,3;

Now I want to get data between a specific time frame, but my following query isn't working

SELECT *
FROM alldata
WHERE (Time BETWEEN DATE_FORMAT(NOW()- INTERVAL 1 SECOND,'%Y-%m-%d %H:%i:%s') AND DATE_FORMAT(NOW()- INTERVAL 5 SECOND,'%Y-%m-%d %H:%i:%s' ))
LIMIT 0,3;

If you see it's the same method as in the first query. But it always returns empty set while I'm 100% sure there exist data in a given time frame.

Upvotes: 0

Views: 59

Answers (2)

Guy Louzon
Guy Louzon

Reputation: 1203

You've replaced the chronological order in the BETWEEN parameters:

BETWEEN works from BEFORE to AFTER, NOW() - 1 is ALWAYS AFTER NOW() - 5

Maybe this will work

SELECT *
FROM alldata
WHERE (Time BETWEEN DATE_FORMAT(NOW()- INTERVAL 5 SECOND,'%Y-%m-%d %H:%i:%s') AND DATE_FORMAT(NOW()- INTERVAL 1 SECOND,'%Y-%m-%d %H:%i:%s' ))
LIMIT 0,3;

Upvotes: 1

Martin Zeitler
Martin Zeitler

Reputation: 76579

the brackets should be alike this (not tested, but it appears rather likely):

SELECT *
FROM alldata
WHERE Time
BETWEEN DATE_FORMAT(NOW() - INTERVAL 5 SECOND,'%Y-%m-%d %H:%i:%s')
AND DATE_FORMAT(NOW() - INTERVAL 1 SECOND,'%Y-%m-%d %H:%i:%s')
LIMIT 0, 3;

Upvotes: 1

Related Questions