homepage Welcome to WebmasterWorld Guest from 54.161.210.58
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Get Records in between specific dates
Get Records in between specific dates
ashishtiwari19

5+ Year Member



 
Msg#: 4191813 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4191813 posted 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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4191813 posted 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved