[Solved] Selecting with date range using timestamp

[eluser]phused[/eluser]
I'm currently storing entries on a table using the unix timestamp.

I have a page on my site where I need to list all entries that are not more than 30 days old. How could I go on doing this taking into consideration the timestamp issue?

Does anyone have any examples of code that can do this or point me out to an article?

(I tried doing a search but didn't find anything, just discussions on which ways to store dates are the best)

[eluser]pistolPete[/eluser]
You can try this:

Code:

// current timestamp - 30 days:
$min_timestamp = time() - (30 * 24 * 60 * 60);
$this->db->where('timestamp_field >', $min_timestamp);
$this->db->get('your_table');

[eluser]cahva[/eluser]
There are 2 kinds of people in this world. Those who use unix timestamp and those who use datetime field. Im datetime people Its much more easier to do date searches from database if you use datetime or timestamp(not unix!). Heres an example:

Code:

SELECT * FROM `yourtable` WHERE datefield > CURDATE() - INTERVAL 30 DAY;

If you have unixtime, you can use FROM_UNIXTIME function to convert it to datetime (Im assuming Mysql).

Code:

SELECT * FROM `yourtable` WHERE FROM_UNIXTIME(datefield) > CURDATE() - INTERVAL 30 DAY;

[eluser]phused[/eluser]
Awesome, thanks for both responses, exactly what I was looking for!

[eluser]cahva[/eluser]
Oh yeah and using CI the same query that pistolpete used would be like this:

Code:

$this->db->where('FROM_UNIXTIME(timestamp_field) > CURDATE() - INTERVAL 30 DAY');
$this->db->get('your_table');

Both ways are perfectly good so just pick whatever you like

[eluser]acipayamli[/eluser]
What about,if I would like to define interval as a php varible?
I hopelessly tried following and it didn't work.

Code:

$this->db->where('time_in > CURDATE() - INTERVAL', $interval, 'DAY');

[eluser]acipayamli[/eluser]
Solved my own question;

using dots instead of commas (to combine all as a string)

Code:

$this->db->where('time_in > CURDATE() - INTERVAL '.$interval.' DAY');