CODEWITHSUNDEEP
phpmysqltimestamp
somejkuser
somejkuser

Reputation: 9040

Select records from 2 weeks prior using timestamps in mysql

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.

Upvotes: 0

Views: 1122

Answers (2)

bishop
bishop

Reputation: 39434

From memory:

SELECT * FROM Foo WHERE bar BETWEEN (NOW() - INTERVAL 2 WEEK) AND NOW()

Replace NOW() with anything that is timestamp related. I am currently wrestling with my tablet so I can't check if this is exactly right. Good luck.

Upvotes: 5

Fluffeh
Fluffeh

Reputation: 33522

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.

Upvotes: 2

Related Questions