Why does this simple SQL statement not work?

Question

dateposted - MySQL TIMESTAMP column:

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

... SHOULD, if I am not mistaken, return the lines where I datepostedwas last week. But he only returns posts in less than one day. I got the impression that I TIMESTAMPused seconds?

IE: 7 * 3600 * 24 = 604800

Answer 1

Using:

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

Answer 2

This is because it is now()implicitly converted to 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

, TIMESTAMP, DATETIME , . UNIX, 1/1/1970.

SQL UNIX MySQL, :

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

NB: NOW() .

---

update: , . NOW() , UNIX. , - . - YYYYMMDDHHMMSS, .

:

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 |
+----+---------------------+------------+-----------------------+

, . UNIX_TIMSTAMP() , :

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 |
+----+---------------------+------------+------------+

, :

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

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

, @OMGPonies, , , , . , TIMESTAMP NOW().

Answer 4

. - . :

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

, DATE_SUB, ADD .

Answer 5

:

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

, mySQL.