MySQL query with UNIX time interval

Question

I need MySQL to write a query where it retrieves all rows where the timestamp unix ("added_on") field is more than 6 months old.

reference

Thanks in advance!

Answer 1

SELECT * FROM yourtable WHERE added_on <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 6 MONTH)) 

Answer 2

 SELECT * FROM mytable WHERE added_on < (NOW() - (6 * 30 * 24 * 60 * 60)); 

Answer 3

The MySQL function DATE_ADD () (aka DATE_SUB) provides your functionality:

  SELECT * FROM table WHERE DATE_ADD(FROM_UNIXTIME(added_on), INTERVAL 6 MONTH) > NOW() 

• http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

... and quite read :-)

Answer 4

 select * from table where now() - interval 6 month > from_unixtime(added_on) 

Answer 5

 SELECT * FROM foobar WHERE added_on < UNIX_TIMESTAMP() - 15778463 

This is not exactly 6 months, since it is slightly different every year, but it should be close enough for each goal ( converted to Google seconds )

You can also convert the UNIX timestamp to a “real” timestamp and use the MySQL date and time functions on it, which will probably be more accurate and look prettier than the integer 15778463 hardcoded in the query (you can use INTERVAL 6 months ), but it will be much slower than working with regular integers.