Hi I am trying to get a row count on the number of entries in my database from two days ago. I have the code below which gets the data from now until two days ago, but i just want the data of all of the entries in the database from midnight to midnight two days ago.
This is the code i have to get from now until two days ago.
SELECT * FROM vistordetails1 WHERE date_time >= ( NOW() - INTERVAL 2880 MINUTE )
could I do something like this
SELECT * FROM vistordetails1 WHERE date_time >= ( INTERVAL 1440 MINUTE - INTERVAL 2880 MINUTE )
Close, give this a shot...
SELECT * FROM vistordetails1
WHERE date_time <= ( NOW() - INTERVAL 1440 MINUTE )
AND date_time >= ( NOW() - INTERVAL 2880 MINUTE )
SQL can be a bit confusing at times. Try to think of this in a programming construct. You want to do something if your value is withen a certain range.
if(val > 1 && val < 10){
//value is between 1 and 10 exclusive
}else{
//value is out side the range
}
SELECT *
FROM visitordetails1
WHERE date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 DAY)
AND DATE_SUB(CURDATE(), INTERVAL 2 DAY)
If you want items between two dates, you need to set both a minimum and maximum:
SELECT * FROM vistordetails1 WHERE date_time >= (NOW() - INTERVAL 2880 MINUTE) AND date_time <= (NOW() - INTERVAL 1440 MINUTE)
You can use the date() function to get rid of the time component:
SELECT *
FROM vistordetails1
WHERE date_time >= ( date(NOW()) - INTERVAL 2880 MINUTE ) and
date_time < date(now())
No, that would actually select entries from 1 day after the start of time (1. Jan. 1970). Try this statement:
SELECT * FROM vistordetails1
WHERE date_time <= ( NOW() - INTERVAL 1440 MINUTE )
AND date_time >= ( NOW() - INTERVAL 2880 MINUTE )
This will consider 2 full days:
select date(now()) as today, date_add(date(now()), interval -2 day) "2 days ago";
I am using the function date to illustrate to you how to get the date part of a complete date time. Adapt to your needs and you will get there.