Adding days to unix timestamp in where clause?

Question

I need to delete all entries in a table where the active column is false and that the expire date being 30 days back or more from current date. But how do I add the 30 days to the timestamp in where CLAUSE? I tried this but get a malformed SQL exception

DELETE * FROM share
         WHERE active = false
         AND (expire + 30) > UNIX_TIMESTAMP();

I could not get timestampadd to work either.

What is your expire column data type?

dkasipovic
– dkasipovic

2018-01-23 09:53:21 +00:00
Commented Jan 23, 2018 at 9:53 — dkasipovic
– dkasipovic, Commented Jan 23, 2018 at 9:53
It is timestamp

Lars Kakavandi-Nielsen
– Lars Kakavandi-Nielsen

2018-01-23 10:00:10 +00:00
Commented Jan 23, 2018 at 10:00 — Lars Kakavandi-Nielsen
– Lars Kakavandi-Nielsen, Commented Jan 23, 2018 at 10:00

juergen d · Accepted Answer · 2018-01-23 09:52:28Z

1

 DELETE FROM share
 WHERE active = false
 AND from_unixtime(expire) + interval 30 day > curr_date();

or since unix time is in seconds:

 DELETE FROM share
 WHERE active = false
 AND expire + (30*86400) > UNIX_TIMESTAMP();

answered Jan 23, 2018 at 9:52

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dkasipovic · Accepted Answer · 2018-01-23 10:09:29Z

0

So, to make sure that MySQL can actually use indexes, you should do something like this:

DELETE FROM share WHERE active = false AND expire <= UNIX_TIMESTAMP() - 30 * 24 * 3600;

answered Jan 23, 2018 at 10:09

dkasipovic

6,1301 gold badge23 silver badges27 bronze badges

Collectives™ on Stack Overflow

Adding days to unix timestamp in where clause?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related