I need to MySQL write a query where it retrieves all rows where the unix timestamp field ("added_on") is more than 6 months old.
Add a comment
|
5 Answers
SELECT *
FROM yourtable
WHERE added_on <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 6 MONTH))
Comments
SELECT * FROM mytable WHERE added_on < (NOW() - (6 * 30 * 24 * 60 * 60));
Comments
MySQL's DATE_ADD() (aka DATE_SUB) function provides your functionality :
SELECT * FROM table WHERE DATE_ADD(FROM_UNIXTIME(added_on), INTERVAL 6 MONTH) > NOW()
... and is quite readable :-)
Comments
select * from table
where now() - interval 6 month > from_unixtime(added_on)
Comments
SELECT * FROM foobar WHERE added_on < UNIX_TIMESTAMP() - 15778463
This isn't exactly 6 months, as its a bit different every year, but it should be close enough for every purpose (converted to seconds by Google)
You can also convert the UNIX timestamp to a "real" timestamp and use MySQL's date/time functions on it, which'll probably be more accurate and looks prettier than having an 15778463 integer hardcoded to the query (you can use INTERVAL 6 months), but it'll also be much slower than working with plain integers.
3 Comments
Mchl
To allow usage of indexes do
WHERE added_on < UNIX_TIMESTAMP() - 15778463 instead
shesek
Updated. I'm used to postgres, who can still understand it and use the index
Mchl
THa'd explain why you think working with 'plain integers' would be much slower than working with plain integers (a.k.a 'real timestamp')