The number of rows in the mysql database, where the timestamp is in the interval X

Question

I am trying to count rows in a table in my database that were inserted within X hours or X days. I tried repeatedly, but I keep getting empty answers.

The start_stamp column in my table is formatted as follows: 2013-08-07 18:18:37

I tried many options: select * from mytable, where start_stamp = now () interval -1 day;

But all to no avail. Can someone tell me what I am doing wrong?

Answer 1

Instead of picking strings where start_stamp is now() - 1day , you need strings where it is greater than or equal to this range. Also, your syntax doesn't work a bit. Date arithmetic in MySQL uses column_value - INTERVAL <number> <period> , so you need to:

 SELECT COUNT(*) AS num_new_rows FROM mytable WHERE start_stamp >= NOW() - INTERVAL 1 DAY 

Similarly, to get n hours ago, use INTERVAL n HOUR

 # Within 3 hours... WHERE start_stamp >= NOW() - INTERVAL 3 HOUR 

The date range arithmetic syntax is described in the small paragraph below the DATE_ADD() help function in the official MySQL documentation.

Answer 2

Here are some examples ...

All records for the last 6 hours:

 select * from mytable where start_stamp >= now() - interval 6 hour; 

All records for the last 2 days:

 select * from mytable where start_stamp >= now() - interval 2 day; 

All entries for the last 2 1/2 days:

 select * from mytable where start_stamp >= now() - interval 2 day - interval 12 hour;