dateposted is really a MySQL TIMESTAMP column:

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

...*SHOULD*, should i be not mistaken, return rows where dateposted was within the last week. However it returns only posts under roughly eventually old. I had been of the opinion that TIMESTAMP used seconds?

IE: 7 * 3600 * 24 = 604800

Use:

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

That's because now() is unconditionally converted to a number from timestamp and mysql conversion rules produce a number like YYYYMMDDHHMMSS.uuuuuu

from mysql paperwork:

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

No, you cannot unconditionally use integer arithmetic with TIMESTAMP, DATETIME, along with other date-related data types. You are considering the UNIX timestamp format, that is an integer quantity of seconds since 1/1/1970.

You are able to convert SQL data types to some UNIX timestamp in MySQL after which use arithmetic:

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

NB: adding zero to [cde] causes it to be return a number value rather than a string value.


update: Okay, I am totally wrong using the above query. Transforming NOW() to some number output does not create a number that may be in comparison to UNIX timestamps. It creates several, however the number does not count seconds or other things. The numbers are simply YYYYMMDDHHMMSS put up together.

Example:

NOW()

It's obvious the number values aren't comparable. However, 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 | +----+---------------------+------------+-----------------------+ may also convert number values for the reason that format as it can certainly convert a string representation of the timestamp:

UNIX_TIMSTAMP()

Now it's possible to operate a query by having an expression evaluating them:

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

However the answer distributed by @OMGPonies continues to be better, as this expression during my query most likely can't take advantage of the index. I am just offering this being an explanation of methods the SELECT * FROM foo WHERE UNIX_TIMESTAMP(dateposted) > UNIX_TIMESTAMP(NOW())-604800 +----+---------------------+ | id | dateposted | +----+---------------------+ | 3 | 2009-12-18 00:00:00 | +----+---------------------+ and TIMESTAMP features work.

Internally possibly. The best way to do this is actually the date math functions. So it might be:

NOW()

I believe there's to start dating ?_SUB, I am used just to presenting ADD everywhere.

Do this query:

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

I'm presuming that you're using mySQL.