'How do I get Timestamp minus 6 weeks in MySQL?

I have a field named timestamp. This is the last time a member was logged in. I am looking to include a where clause in a query for something like

WHERE timestamp > todays date - 6 weeks

How would I do this? I am trying to only include users that have logged in in the last 6 weeks.

Thanks

mysqltimestamp


Solution 1:[1]

I find this syntax more readable than date_sub, but either way works.

WHERE timestamp >= NOW() - INTERVAL 6 WEEK

If you want to go by "Today" (midnight) instead "now" (current time), you would use this

WHERE timestamp >= DATE(NOW()) - INTERVAL 6 WEEK

Solution 2:[2]

where column>=date_sub(now(), interval 6 week)

Solution 3:[3]

This link demonstrates how you might acquire a timestamp of yesterday using the format DATE_ADD(CURDATE(), INTERVAL -1 DAY), therefore your query would probably be:

WHERE timestamp > DATE_ADD(CURDATE(), INTERVAL -42 DAY)

Solution 4:[4]

You can use between and now():

select somevalue 
from yourtable
where yourtimestamp between now() - interval 1 day and now()

Solution 5:[5]

for TIMESTAMP there is a TIMESTAMPADD() function

SELECT TIMESTAMPADD(WEEK, -6, CURRENT_TIMESTAMP)

this will return the timestemp of 6 weeks ago

or in the case like the question

SELECT * FROM users
WHERE lastlogin > TIMESTAMPADD(WEEK, -6, CURRENT_TIMESTAMP)

Solution 6:[6]

Any luck yet. Have you tried:

>= DATE_SUB(NOW(), INTERVAL 6 WEEK)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 ajreal
Solution 3 Neil
Solution 4 Eric Leschinski
Solution 5
Solution 6 rd42

Related Questions