SELECT BY HOUR? Datetime related

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
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

SELECT BY HOUR? Datetime related

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

Just like PHP, Mysql has a Online Manual.

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

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

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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
Post Reply