February 10th, 2013, 06:41 PM
Within: INTERVAL, BETWEEN, DATESUB Urgh!
I should know this, but I am having a hell of a time with a query. Am I crazy?
Got a few records with these timestamps:
I am trying to count how many records came in 20 minutes prior to 2013-02-03 21:15:25 (which should be zero)
SELECT COUNT(*) from xferleads where timestamp > '2013-02-03 21:15:25' - INTERVAL 20 MINUTE AND customer = 1
The interval just won't give me the right count. Even at one minute I get 2 counts on the above dataset.
February 10th, 2013, 06:51 PM
Your query only looks for stuff where the timestamp is after 20:55. It says nothing about the timestamp also being before 21:15.
Note you can't (easily) use BETWEEN because you don't want to include the record at exactly timestamp=21:15.
SELECT COUNT(1) FROM xferleads WHERE timestamp >= '2013-02-03 21:15:25' - INTERVAL 20 MINUTE AND timestamp < '2013-02-03 21:15:25' AND customer = 1