Disinct Times from a list of Dates/Times

[neofactor]

Hello...

I have question.

I have a list of date/times in the database and want to be able to COUNT the distinct hours from a date/Time feild for a paticulare date range.

Imagine this... A database (MS Access) with Date/Time feild and I want a tally of all the ones that are at 1 am, 2 am, all the way to Midnight (24:00 hour scale is fine)

So it would list like this:

 time  count
  1     30
  2     12
  3     0
  4     11
  .     .
  24    15

Thank you!
David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[CFHub]

There is a script here that breaks the counts (hits in this case) into 10 minute intervals. You could easily change it to 1 hour intervals.

http://cfhub.com/forum//index.cfm?FuseAction=Thread&TopicID=2399

 

[r937]

the methods use in the cfhub scripts are inefficient, since they return all the detailed records from the database and do the counting in the script

what if it were a few million instead of a few hundred rows? is it not better to have the database counting them and sending over only max 24 rows (the counts per hour)?

select hour(thedate), count(*)
from thetable
group by hour(thedate)

rudy

http://rudy.ca/

 

[neofactor]

Wow.. that did the trick exactly... I had this Huge code in CF doing it and it was so simple in SQL.

Thanks-

I have one last question:
I have a hit counter that tracks IP address and the time they came. I sort the count by the days of the week.. like MON TUE, ect.

I use this code:

SELECT DatePart('w', hit_time) as xday, count(*) as xcount
FROM tb_hittracker
GROUP BY DatePart('w', hit_time)


This works great if I want the count of all hits, but I want to count only DISTINCT IP addresses per day. I tried this:

SELECT DatePart('w',hit_time) AS xday, count(hit_ip) as XCOUNT
FROM tb_hittracker
GROUP BY DatePart('w', hit_time) , tb_hittracker.hit_ip;


But this gives me the wrong results...
Any ideas... I tried DISTINCT hit_ip but get errors.
I thought of doing a Query on Query that pulled distict ips into a count... but there has to be a way to do it in one query.

(Access Database BTW)

Thanks... David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[webmigit]

I think I got it by just calling the output in a group=xday

I am doing testing on it now but it looks like it is working ok.

 

[webmigit]

I take that back.. it is not accurate... still working on it!

 

[neofactor]

Distinct IP might not be the answer!

If you do this... that means it will most likely clip your results to include only the day that the IP address 1st came to your site, and not show if they came back again another day.

It would require you check for distinct IP addresses within a certain day only and not for a distinct ip for all days.

Just a thought. David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[r937]

david, please repeat what you are trying to get this time with the distinct ip numbers by day -- i don't understand why you need to test for distinct if you're counting hits for each ip, because grouping in sql does that automatically

(have i won any converts over to the "don't write code for stuff the database can do in sql" camp yet/)

rudy

 

[neofactor]

(The two prior posts listed by me in this thread are actually dmcintosh, somehow the system switched he and I..)

That should clear up some confusion. David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[neofactor]

It did it again... ok... let me explain

posts by webmigit = by dmcintosh, the above post is by webmigit... David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[neofactor]

RUDY:

I am tracking page hits in the database. IP, Date/Time, URL

I want to list the number of Unique hits for day of the week (Mon, Tue, Wed, ect...) and list the Time of day.

I currently have no problem csorting and counting, but I want unique hits only... Not if a user clicked 20x but that they are counted only once for that hour, or that day.

Unique IP address hit vs. Page hits.

Does that clear it up at all?

The problem is this:
Lets say a user clicks a page Monday @ 8am & 10 pm and then Tuesday at 1 and 3 pm. How would I count their hit for for Monday as only 1 hit and Tuesday as 1 hit.

If I group by IP SQL above will get it wrong.. they will be listed on Monday, but not Tuesday since that IP address was already counted.
David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: "This Post was Helpful"

 

[r937]

ah, the number of distinct ip numbers each day

too bad access can't do it the normal way --

select datePart('w', hit_time) as xday
     , count(distinct hit_ip) as xcount
  from tb_hittracker 
group by datePart('w', hit_time)

for some reason, access doesn't support count(distinct xxx) -- at least, access 97 didn't, dunno about later versions, maybe they fixed it, give it a try

in any case there's an easy workaround

first, you need a query to count how many times each ip number is present every day

select datePart('w', hit_time) as xday
     , hit_ip  
     , count(*) as ipcount
  from tb_hittracker 
group by datePart('w', hit_time), hit_ip

actually, you may not need the number of times, but it doesn't hurt, because what you do with this query is not run it, but rather, save it, let's say as q_xdayhits

then you query the query

select xday
     , count(*) as distinctips
  from q_xdayhits
group by xday