Selecting different time periods from SQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Selecting different time periods from SQL Query

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting different time periods from SQL Query

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Selecting different time periods from SQL Query

Post 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!
Last edited by defroster on Mon Aug 09, 2010 4:48 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting different time periods from SQL Query

Post by VladSun »

You should compare videos.dateposted with "current date - 24 hours".
There are 10 types of people in this world, those who understand binary and those who don't
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Selecting different time periods from SQL Query

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting different time periods from SQL Query

Post by Eran »

Code: Select all

videos.dateposted > NOW() - INTERVAL 24 HOUR
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

Re: Selecting different time periods from SQL Query

Post by defroster »

Thanks! Now it is working
Post Reply