MySQL query that returns all rows that have been added in the last 24 hours
I have a table that contains the following columns:
ip(varchar 255), index(bigint 20), time(timestamp)
every time something is inserted there, the time column gets the current timestamp. I want to run a query that returns all rows that have been added in the last 24 hours. Here's what I'm trying to accomplish:
SELECT ip, index FROM users WHERE ip = 'some ip' AND TIMESTAMPDIFF(HOURS,time,NOW()) < 24
And it doesn't work.
a source to share
CLOCK should be CLOCK. See TIMESTAMPADD documentation for valid parameter values unit
:
TIMESTAMPADD (unit, interval, datetime_expr)
Adds an integer range expression to a date or time expression datetime_expr. The unit for the interval is specified by the unit argument, which must be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
So your request should be:
SELECT ip, index
FROM users
WHERE ip = 'some ip'
AND TIMESTAMPDIFF(HOUR, time, NOW()) < 24
Note that this query will not be able to use the index in time if it exists. It might be more efficient to rewrite the query like this:
SELECT ip, index
FROM users
WHERE ip = 'some ip'
AND time > NOW() - interval 1 day
a source to share
Use mysql timing functions
SELECT something FROM tbl_name
WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= time;
a source to share
You might want to do it the other way around. Instead of subtracting 24 hours each time, calculate the time 24 hours ago (once) and then check your time values against that. Most likely more optimal, although I am not too familiar with mySql
SELECT ip, index FROM users WHERE ip = 'some ip' AND time > DATE_SUB(NOW(), HOUR, 24)
a source to share
From http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff
TIMESTAMPADD (unit, interval, datetime_expr)
Adds an integer expression range to a date or datetime datetime_expr. The device for intervals is specified by a one argument, which must be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER OR YEAR.
As of MySQL 5.1.24, it is possible to use MICROSECOND FRAC_SECOND with this function and FRAC_SECOND is deprecated. FRAC_SECOND is removed in MySQL 5.5.
The value of one can be specified using one of the keywords as shown, or using the SQL_TSI_ prefix. For example, photo studio and SQL_TSI_DAY are both 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
a source to share