CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 51

MySQL query with UNIX timestamp field

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.

Upvotes: 3

Views: 13879

Answers (5)

shesek
shesek

Reputation: 4682

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.

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

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

Upvotes: 2

domi27
domi27

Reputation: 6923

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 :-)

Upvotes: 2

Marc B
Marc B

Reputation: 360562

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

Upvotes: 7

Fabio Cicerchia
Fabio Cicerchia

Reputation: 679

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

Upvotes: 2

Related Questions