Help with MySQL query

Question

I have a table that contains the next columns:

ip(varchar 255), index(bigint 20), time(timestamp)

each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute:

SELECT ip, index FROM users WHERE ip = 'some ip' AND TIMESTAMPDIFF(HOURS,time,NOW()) < 24

And it doesn't work. Can someone help me out? Thanks :)

Answer 1

HOURS should be HOUR. See the documentantion for TIMESTAMPADD to see the valid values of the unit parameter:

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

So your query should be:

SELECT ip, index
FROM   users
WHERE  ip = 'some ip'
AND    TIMESTAMPDIFF(HOUR, time, NOW()) < 24

Note also that this query won't be able to take advantage of an index on time, if one exists. It could be more efficient to rewrite the query as follows:

SELECT ip, index
FROM   users
WHERE  ip = 'some ip'
AND    time > NOW() - interval 1 day

Answer 2

Use date time functions of mysql

SELECT something FROM tbl_name
    WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= time;

Answer 3

From http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Beginning with MySQL 5.1.24, it is possible to use MICROSECOND in place of FRAC_SECOND with this function, and FRAC_SECOND is deprecated. FRAC_SECOND is removed in MySQL 5.5.

The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

-> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

-> '2003-01-09'

So ...

SELECT TIMESTAMPADD(DAY,-1,CURRENT_TIMESTAMP());

should be what you want

Answer 4

You might want to consider doing it the other way round. Rather than subtract 24 hours from each time, calculate the time 24 hours ago (once), and then check your time values against that. Very likely more optimal, though I'm not too familiar with mySql

SELECT ip, index FROM users WHERE ip = 'some ip' AND time > DATE_SUB(NOW(), HOUR, 24)

Answer 5

SELECT ip, index
FROM users
WHERE ip = 'some ip'
AND time >= NOW() - INTERVAL 24 HOUR

This assumes that you do not have "future" times in your table. If there are, append this to your where clause:

AND time <= NOW()