stack stack - 2 months ago 10
SQL Question

How can I select rows that are since between last hour and last year?

This query restricts selecting since last hour:

... WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 HOUR))


And this one does that for last year:

... WHERE date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))


How can I write that
WHERE
clause to restricts selecting between last hour and last year ?

Answer

use a between:

WHERE from_unixtime(date_time) BETWEEN (now() - INTERVAL 1 year) AND (now() - INTERVAL 1 hour)

comment followup:

mysql> select now() - interval 1 year, date_sub(now(), interval 1 year);
+-------------------------+----------------------------------+
| now() - interval 1 year | date_sub(now(), interval 1 year) |
+-------------------------+----------------------------------+
| 2015-09-09 09:25:59     | 2015-09-09 09:25:59              |
+-------------------------+----------------------------------+
1 row in set (0.00 sec)
Comments