Link to home
Start Free TrialLog in
Avatar of bascdfj
bascdfj

asked on

Need to get Time Range in log file from timestamp entries

Hi All -
I have a table with timestamp entries as follows:

UserID
Timestamp

This table is written to every time a user does certain things in the application (like moving between certain pages in the web application). I want to see an aggregate of activity within a certain range of time.

For example, for each day in a time period (say between july 1 and dec 31), I want to see any activity between the first entry for the day and the last entry for the day grouped by hour.

Example:

For Oct 1st, person A showed 5 activity entries between 8:am and 9:am, 12 between 9am and 10am, 7 between 11am and 12 noon, and 22 between 2:pm and 3pm.

Ideally this would show results only for days that have activity, and only hour periods that have activity. This way I can see if there are any big holes in activity during a give day.

Is there a way to do this entirely with SQL?

Many thanks
Ford

Avatar of chapmandew
chapmandew
Flag of United States of America image

try this:

select
userid, hourtime = datepart(h, timestamp), counters = count(*)
from yourlogtable
where timestamp >= @date1 and timestamp < @date2
group by userid, datepart(h, timestamp)
order by 2 asc
If timestamp is of the data type TIMESTAMP it will not do what you desire.  Otherwise, use chap's suggestion.
that is correct...but a timestamp isn't really a time field at all that you can query, so we wouldn't be able to get a date from it anyway...
for example, for me this returns the value of 0x0000000000070433 in the timestamp column

create table #t(id int, t timestamp)

insert into #t(id)
select 5

select * from #t

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = 'july 1, 2008'
SET @endDate = 'dec 31, 2008' + 1 --so that < can be used in comparison

SELECT UserID, DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0), COUNT(*) AS Activity
FROM tablename
WHERE Timestamp >= @startDate AND Timestamp < @endDate
GROUP BY UserID, DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0)
ORDER BY UserID, DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0)
CORRECTION:
    SET @endDate = DATEADD(DAY, 1, 'dec 31, 2008') --so that < can be used in comparison


And since the default format for date is rather ugly, you prob want to do something to "cutesy" it up:


DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = 'july 1, 2008'
SET @endDate = DATEADD(DAY, 1, 'dec 31, 2008') --so that < can be used in comparison

SELECT
    UserID,
    CONVERT(VARCHAR(20), DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0), 100) AS Hour,
    COUNT(*) AS Activity
FROM tablename
WHERE Timestamp >= @startDate AND Timestamp < @endDate
GROUP BY UserID, CONVERT(VARCHAR(20), DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0), 100)
ORDER BY UserID, CONVERT(VARCHAR(20), DATEADD(HOUR, DATEDIFF(HOUR, 0, Timestamp), 0), 100)
Avatar of bascdfj
bascdfj

ASKER

Scott -
Your code works great! Only drawback is not in date order. Is there an easy way to summarize to only show if there was activity on a day? Like roll results for each day into one entry rather than by hour?

Many Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bascdfj

ASKER

Excellent - just what I need. Thanks Again!
You shouldn't have your answer as the "Accepted" solution because it's not a solution.  It just says thanks.  Just split the points how you feel they should be allocated.
Avatar of bascdfj

ASKER

Not sure why the weird closing issue... my comments are definitely not the solution - I guess I am not completely familiar with the process - solution should be Scotts answer with share point to other two.

I'm not familiar with how it looks from your side, but you should see something that says "split".
Avatar of bascdfj

ASKER

Hope this will split 450 to Scott - 25 to other two folks...