[RESOLVED] Date query Help

UKlee

Hi all I am trying to extract some information from a mysql database.
Basicaly i am trying to count how many items where submited on a monday tuesday etc I have looked around but have been unable to find a script or snippet that would help me achieve this. i am running PHP Version 4.3.11 and Mysql Version 3.23.49.

my dates are stored using the datetime format 2006/20/12 00:00:00 and am wondering how i can count these based on the day it was entered.

Likewise I would like to show perhaps the most popular time filed for example 00:00:00 - 02:00:00 and so on in like 2 hour chuncks if this makes sence.

Any help or advice would be great

Have a great christmas and New year
Happy holidays all

Lee

bradgrafelman

To see how many dates per day appear, I would use a query such as this one:

SELECT COUNT(*) AS `count`, DATE_FORMAT(`d`, '%W') AS `day`,
DATE_FORMAT(`d`, '%w') AS `day_int`
FROM `asdf`
GROUP BY `day`
ORDER BY `day_int` ASC

As for your block-of-time question, I'm afraid I can't think of an easy solution off the top of my head. Then again, I'm not database guru...

Roger_Ramjet

Same as for day but you do it on the hour field - if your datetime column stores the time as well that is.

UKlee

Thank you very much Guys worked a dream and the hours setup works fine using this also.

Merry Christmas and happy holidays to you both.

Kind regards

Lee