CODEWITHSUNDEEP
phpmysqldatetimestamp
Smash
Smash

Reputation: 513

How to select a records with timestamp ranges?

I mean that if I have table like this:

id | time        | name
1  | 1354382314  | test1
2  | 1374769114  | test2
3  | 1322759914  | test3

How to select a records, for example, that was created a week ago, month ago or year ago? Is it possible only with mysql functions or how can I do it in php?

Upvotes: 0

Views: 60

Answers (3)

wusuopubupt
wusuopubupt

Reputation: 64

select id,time,name from your_table where (current_timestamp-time)>7*24*60*60;

7*24*60*60 stands for a week

Upvotes: 1

Okan Kocyigit
Okan Kocyigit

Reputation: 13441

I think it's also possible with mysql functions

Like,

select * from table where time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK))
select * from table where time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))
select * from table where time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH))

Upvotes: 1

MightyPork
MightyPork

Reputation: 18901

Since timestamp is a number that always grows, you can simply calculate the start and end stamp of your requested range, and use

WHERE `time` >= 'startstamp' AND `time` <= 'endstamp'

To get a stamp 1 week ago, you can use php functoon strtotime("-1 week"). Similar with month etc.

If you need current stamp for anything, use time().

Upvotes: 1

Related Questions