Welcome to WebmasterWorld Guest from 54.160.131.144

Forum Moderators: open

Message Too Old, No Replies

Get Records in between specific dates

Get Records in between specific dates

     

ashishtiwari19

7:44 am on Aug 25, 2010 (gmt 0)

5+ Year Member



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

3:38 pm on Aug 25, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

3:44 pm on Aug 25, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You **may** need to use the mysql date format:

.... Timestamp >= '2010-01-01' AND Timestamp <= '2011-01-01'

yyyy-mm-dd
 

Featured Threads

Hot Threads This Week

Hot Threads This Month