使用mysql中的时间戳选择2周前的记录

Question

I have records in a table that are stored using php's strtotime function. What I need and don't have any experience with is querying the database for all records within 2 weeks.

I'm sorry I have no code to show for as using timestamps is all so new to me.

Answer 1

If your date is stored as a timestamp, it's easy enough to take two weeks off it:

select
    *
from
    yourTable
where
    dateField<=UNIX_TIMESTAMP()
    and dateField>=(SELECT UNIX_TIMESTAMP()- 60*60*24*14)

As these are all in seconds, just subtract 60 seconds x 60 minutes x 24 hours x 14 days and you have your required condition.

FYI: A timestamp is simply the number of seconds that have passed since the unix epoch so addition or subtraction is really rather simple. You can also use PHP's mktime to generate a timestamp if you want a particular date.