Get Records in between specific dates or time

[ashishtiwari19]

Hi To All,

I have a table with about 5 million records, the structure given below

Primaryid Ipaddress TimeStamp
1 127.0.0.1 2010-08-15 22:23:37
2. 128.0.0.1 2010-08-15 22:23:38
3. 126.0.2.3 2009-09-10 21:23:37

and so on.
Now i want to generate a report with
Number of hits in 2010
Number of hits in Aug 2010
Number of hits on 15 Aug 2010
Number of hits in last 1 hour


am using php to display the report.
has anyone got any idea how to do this ? if it is required , i can change the table structure & import the old data in new table structure, but i need to generate this kind of report. so if it is required , suggest with a better structure.

Thanx in advance

[Anyango]

check FROM_UNIXTIME mysql function, but your dates seem to be stored as datetime string and not timestamp, so that one might not work but you will find alot of datetime functions in mysql date time functions manual

[morehawes]

There are lots of different ways of doing this in MySQL. A few examples ...

Number of hits in 2010 :

SELECT COUNT(*)
FROM table_name
WHERE YEAR(date) = '2010'

Number of hits in last 1 hour :

SELECT COUNT(*)
FROM table_name
WHERE date > DATE_SUB(CURDATE(), INTERVAL 1 HOUR)

Number of hits in Aug 2010 :

SELECT COUNT(*)
FROM table_name
WHERE date BETWEEN '2010-08-01' AND '2010-08-31'

Number of hits on 15 Aug 2010

SELECT COUNT(*)
FROM table_name
WHERE date LIKE '2010-08-15%'

There is a good post about this here [webmasterworld.com...]

Of course this is just the SQL for the queries, you will need to use PHP to run them but there is loads of info and examples on this. You may also find PHP useful when it comes to inserting the date/date ranges into your queries dynamically.