Get data from MYSQL from two days ago

Question

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 )

score 2 · Accepted Answer · edited May 10 '13 at 00:19

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
}

peterm · Answer 2 · 2013-05-08T01:44:42.017

2

SELECT *
  FROM visitordetails1
WHERE date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 DAY) 
                    AND DATE_SUB(CURDATE(), INTERVAL 2 DAY)

SQLFiddle

edited May 08 '13 at 01:44

answered May 08 '13 at 01:37

peterm

91,357
15
148
157

score 1 · Answer 3 · answered May 08 '13 at 01:38

1

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())

answered May 08 '13 at 01:38

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered May 08 '13 at 01:38

0

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)

answered May 08 '13 at 01:38

Mark Parnell

9,175
9
31
36

score 0 · Answer 5 · answered May 08 '13 at 01:38

0

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 )

answered May 08 '13 at 01:38

likeitlikeit

5,563
5
42
56

Also have a look at [this answer](http://stackoverflow.com/a/7756832/1553481) for a more elegant, slightly different way. – likeitlikeit May 08 '13 at 01:44

score 0 · Answer 6 · answered May 08 '13 at 01:41

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.

Get data from MYSQL from two days ago

6 Answers6