Page 1 of 1

SELECT BY HOUR? Datetime related

Posted: Tue Sep 18, 2007 7:59 am
by Smasher
Currently I can select by month and year

SELECT FROM db.table WHERE MONTH(datetimefield) = 4;
SELECT FROM db.table WHERE YEAR(datetimefield) = 4;

However, how would I select by hour or date? Hour more importantly.

Posted: Tue Sep 18, 2007 8:46 am
by Zoxive
Just like PHP, Mysql has a Online Manual.

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Posted: Tue Sep 18, 2007 10:46 am
by Smasher
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Okay so its 

Day(), Month(), Year(), Hour()

Now I have a different question.

I want to get a count of how many users were created per day between an entered time period.

So far I have...

[syntax="sql"]SELECT * FROM db.`table` WHERE `created` > '$dateOne' AND `created` < '$dateTwo'
Somehow I think I need to implement count and group by, any advice welcome!


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Sep 18, 2007 11:10 am
by VladSun
Smasher wrote: SELECT * FROM db.`table` WHERE `created` > '$dateOne' AND `created` < '$dateTwo'

Somehow I think I need to implement count and group by, any advice welcome!
You've already given yourself a pretty good advice - try it :)

Posted: Wed Sep 19, 2007 4:48 am
by Smasher
Okay so I've got this

Code: Select all

"SELECT COUNT(`id`) as `No`, `created` FROM db.`table` WHERE `created` > '$dateOne' AND `created` < '$dateTwo' GROUP BY DATE(`created`) "

However its only returning days that have a count of over one, is there away to return EVERY day? I need a list which includes every day.

Thanks! :D