query: "count" each minute between start/end

From: Nikolas Tautenhahn <virtual_at_gmx.de>
Date: Fri, 27 Jul 2007 22:58:34 +0200
Message-ID: <f8dm6f$qpk$01$1@news.t-online.com>

Hi there...

I have a table with access statistics with columns representing the [date] when a user first connected and the duration[seconds] they stayed.

For a graphical report I need to create a view which shows how many users were online each minute of the day, i.e. say we have this raw data:

user1: entered today, 9:30:17, stayed for 200 seconds (so he needs to be counted for 9:31, 9:32, 9:33 as he left 9:30:37). user2: entered today, 9:28:10, stayed for 180 seconds (so he needs to be counted for 9:29, 9:30, 9:31) and what I would like to get from this raw data is something like:

9:29, 1
9:30, 1
9:31, 2
9:32, 1
9:33, 1

Let's name the connect date when a user entered "c_date" and the duration simply "duration"
how could I do something? I can calculate the [date] when a user left by c_date + (1/86400)*duration and I guess I would need something like between, but I can't put my finger on how I could increment the counter for all whole minutes between c_date and the end-date...

thanks for all help,
N. Received on Fri Jul 27 2007 - 15:58:34 CDT