Why won't this simple SQL statement work?

Question

dateposted is a MySQL TIMESTAMP column:

 SELECT * 
   FROM posts 
  WHERE dateposted > NOW() - 604800

...*SHOULD*, if I am not mistaken, return rows where dateposted was in the last week. But it returns only posts less than roughly one day old. I was under the impression that TIMESTAMP used seconds?

IE: 7 * 3600 * 24 = 604800

Answer 1

Use:

WHERE dateposted BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW()

Answer 2

That is because now() is implicitly converted into a number from timestamp and mysql conversion rules create a number like YYYYMMDDHHMMSS.uuuuuu

from mysql docs:

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

Answer 3

No, you can't implicitly use integer arithmetic with TIMESTAMP, DATETIME, and other date-related data types. You're thinking of the UNIX timestamp format, which is an integer number of seconds since 1/1/1970.

You can convert SQL data types to a UNIX timestamp in MySQL and then use arithmetic:

SELECT * FROM posts WHERE UNIX_TIMESTAMP(dateposted)+604800 > NOW()+0;

NB: adding zero to NOW() makes it return a numeric value instead of a string value.

---

update: Okay, I'm totally wrong with the above query. Converting NOW() to a numeric output doesn't produce a number that can be compared to UNIX timestamps. It produces a number, but the number doesn't count seconds or anything else. The digits are just YYYYMMDDHHMMSS strung together.

Example:

CREATE TABLE foo (
  id SERIAL PRIMARY KEY,
  dateposted TIMESTAMP
);

INSERT INTO foo (dateposted) VALUES ('2009-12-4'), ('2009-12-11'), ('2009-12-18');

SELECT * FROM foo;

+----+---------------------+
| id | dateposted          |
+----+---------------------+
|  1 | 2009-12-04 00:00:00 |
|  2 | 2009-12-11 00:00:00 |
|  3 | 2009-12-18 00:00:00 |
+----+---------------------+

SELECT *, UNIX_TIMESTAMP(dateposted) AS ut, NOW()-604800 AS wk FROM foo

+----+---------------------+------------+-----------------------+
| id | dateposted          | ut         | wk                    |
+----+---------------------+------------+-----------------------+
|  1 | 2009-12-04 00:00:00 | 1259913600 | 20091223539359.000000 |
|  2 | 2009-12-11 00:00:00 | 1260518400 | 20091223539359.000000 |
|  3 | 2009-12-18 00:00:00 | 1261123200 | 20091223539359.000000 |
+----+---------------------+------------+-----------------------+

It's clear that the numeric values are not comparable. However, UNIX_TIMSTAMP() can also convert numeric values in that format as it can convert a string representation of a timestamp:

SELECT *, UNIX_TIMESTAMP(dateposted) AS ut, UNIX_TIMESTAMP(NOW())-604800 AS wk FROM foo

+----+---------------------+------------+------------+
| id | dateposted          | ut         | wk         |
+----+---------------------+------------+------------+
|  1 | 2009-12-04 00:00:00 | 1259913600 | 1261089774 |
|  2 | 2009-12-11 00:00:00 | 1260518400 | 1261089774 |
|  3 | 2009-12-18 00:00:00 | 1261123200 | 1261089774 |
+----+---------------------+------------+------------+

Now one can run a query with an expression comparing them:

SELECT * FROM foo WHERE UNIX_TIMESTAMP(dateposted) > UNIX_TIMESTAMP(NOW())-604800

+----+---------------------+
| id | dateposted          |
+----+---------------------+
|  3 | 2009-12-18 00:00:00 |
+----+---------------------+

But the answer given by @OMGPonies is still better, because this expression in my query probably can't make use of an index. I'm just offering this as an explanation of how the TIMESTAMP and NOW() features work.

Answer 4

Internally perhaps. The way to do this is the date math functions. So it would be:

SELECT * FROM posts WHERE dateposted > DATE_ADD(NOW(), INTERVAL -7 DAY)

I think there is a DATE_SUB, I'm just used to using ADD everywhere.

Answer 5

Try this query:

SELECT * FROM posts WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) < dateposted;

I am assuming that you are using mySQL.