| Get Records in between specific dates
Get Records in between specific dates |
ashishtiwari19
msg:4191815 | 7:44 am on Aug 25, 2010 (gmt 0) |
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
|
LifeinAsia
msg:4192010 | 3:38 pm on Aug 25, 2010 (gmt 0) |
Example:
SELECT COUNT(*)
FROM YourTable
WHERE Timestamp >= '1/1/2010' AND Timestamp <= '1/1/2011'
This will give you the count in 2010. You can get the other counts with similar queries. Some databases also support the BETWEEN operator (e.g., WHERE Timestamp BETWEEN '1/1/2010' AND '1/1/2011').
Note- "timestamp" may be a reserved word in your database, so you may want to change the field name to a non-reserved word.
Also, make sure you have an inde3x on the Timestamp field. Otherwise, the query will take a long time.
|
rocknbil
msg:4192013 | 3:44 pm on Aug 25, 2010 (gmt 0) |
You **may** need to use the mysql date format:
.... Timestamp >= '2010-01-01' AND Timestamp <= '2011-01-01'
yyyy-mm-dd
|
|
|
