Page 1 of 1

Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 4:16 am
by defroster
Hello,

I have the following query:

Code: Select all

$sql = "Select photos.*, categories.cat FROM photos, categories
	WHERE photos.cat_id = categories.id AND photos.status ='1'	ORDER BY (videos.up-videos.down) DESC
	LIMIT $start, $limit";
	$result = mysql_query($sql);
The photos table contains one column called "dateposted' which is in the following format (2010-08-03 00:15:00).

How do I from this query select different timeframes by using "hours" something like this I am guessing??:

Code: Select all

Day
 WHERE dateposted=time(now)-24(hours)
Week
 WHERE dateposted=time(now)-168(hours)
Month
 WHERE dateposted=time(now)-744(hours)
Year
 WHERE dateposted=time(now)-8760(hours)
Thanks so much. /df

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 4:28 am
by VladSun

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 4:41 am
by defroster
Thanks so much, but now I am stuck again. How do I do two 'AND' in a SQL Query.. Sorry I am not too good at this. Still learning.

Code: Select all

$sql = "Select videos.*, categories.cat FROM videos, categories
	WHERE videos.cat_id = categories.id AND photos.status ='1' AND DATE_SUB(videos.dateposted, INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
	LIMIT $start, $limit";
	$result = mysql_query($sql);
I have tried this but it still selects all videos instead of the past 24 hours.. Thanks!

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 4:44 am
by VladSun
You should compare videos.dateposted with "current date - 24 hours".

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 4:50 am
by defroster
Thanks for reply. Yes exactly, but I am a bit puzzled about how to write the exact syntax..? Any ideas? Thanks so much

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 5:53 am
by Eran

Code: Select all

videos.dateposted > NOW() - INTERVAL 24 HOUR

Re: Selecting different time periods from SQL Query

Posted: Mon Aug 09, 2010 7:51 pm
by defroster
Thanks! Now it is working