Geek Answers Handbook

MySQL failed to receive messages in the last 24 hours and last 60 minutes

Ok Editing this ...

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 24 HOUR )
AND CURDATE( )
LIMIT 0 , 30

This is an example of a query that I am trying to run to find messages in the last 24 hours. In the last 60 minutes, I also run a separate one for different needs. The problem is that in the table I'm testing, there are at least 4 rows, of which 3 refer to a 24-hour sentence.

Edit

Ok, so I figured out my problem, 1 Im to curse fatigue. 2 Awful use of Between and Date_Sub .. This has not dawned on me so far, I should have used the name col, where I have CURDATE () when answering my question below.

This is what the timestamp in the database looks like, the standard DATETIME .. 2011-09-01 13:20:08

while I do not give results.

0
5

BETWEEN , :

expr BETWEEN min AND max

:

...BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 hour) AND CURDATE()

>.

+3
$query = "SELECT * FROM votelog WHERE ID=".(int)$_GET['id']." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted > DATE_SUB(CURDATE(), INTERVAL 24 hour)";

: , mysql_real_escape_string($_GET['id']), (int)$_GET['id'].

+1

? BETWEEN AND, ...

$query = "SELECT * FROM votelog WHERE ID=".mysql_real_escape_string($_GET['id'])." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 HOUR) AND DATE_SUB(CURDATE(), INTERVAL 60 MINUTE)";

,

 $query = "SELECT * FROM votelog WHERE ID=".mysql_real_escape_string($_GET['id'])." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR) AND datevoted <= DATE_SUB(CURDATE(), INTERVAL 60 MINUTE)";
+1
SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND 
DATE_SUB( datevoted , INTERVAL 24 HOUR )
LIMIT 0 , 30

, , ... .

0

, , ( , ). CURDATE(). CURDATE() 2011-09-03 (!), datetime, 2011-09-03 00:00:00 (!), ( ) , :

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN DATE_SUB( `2011-09-03 00:00:00` , INTERVAL 24 HOUR )
AND `2011-09-03 00:00:00`
LIMIT 0 , 30

, :

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN `2011-09-02 00:00:00` AND `2011-09-03 00:00:00`
LIMIT 0 , 30

, .

NOW():

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
  AND datevoted
      BETWEEN DATE_SUB( NOW( ) , INTERVAL 24 HOUR )
          AND NOW( )
LIMIT 0 , 30

:

MySQL docs: DATETIME, DATE TIMESTAMP

MySQL docs:

SO: datetime vs timestamp

0

More articles:


All Articles