SQL results by minute?

August 25, 2006 10:41 AM

Looking to group SQL by a certain time interval. How?

I have a datetime column indicating login time. I want to find out how the data is grouped by the minute, essentially giving me a result of each minute and how many rows are associated with that minute.

I know that I have to use DATEDIFF, but I'm not clear what the syntax/concept is for using it in a grouping.

posted by Four Flavors to Computers & Internet (14 answers total)

I'm not sure exactly what you're asking. Are you saying you want to segment the login times by the minute of the hour, excluding what hour it is? That is, would you want to know how many people logged in at the hour precisely, how many at 1 minute past, etc.?

If so, and you're using SQL Server (as I surmise from the mention of DATEDIFF), you probably want to use DATEPART. You'd do something like this:

SELECT DATEPART(mi, login_time) AS login_minute, count(*)
FROM LOGIN_TBL
GROUP BY DATEPART(mi, login_time)
posted by cerebus19 at 10:52 AM on August 25, 2006

Wouldn't you just GROUP BY login_minute?
posted by evariste at 10:58 AM on August 25, 2006

evariste: That (grouping by an alias) doesn't work with some databases, and I can never remember which ones those are. It shouldn't make a difference to the performance, though.
posted by cerebus19 at 10:59 AM on August 25, 2006

I want to find out how many logins per minute, so an intermediary step is to get a grouping of all logins by minute.

What you've got is close- it gives me the total number of logins across all months/days/hours by minute 1-60.

I want to keep the Month/day/hour as well, so that I can determine by looking at the results what a maxiumum login/minnute is, and then later select on the time period surrounding that to see what an average login/minute is when running 'under load'.

to rephrase: I want to see how many logins happened per minute for every single minute ever in my first set of results.
I will then refine that by looking for the max logins/min and run a query for the time surrounding that period to determine what the average is when the system is loaded.

Yes, using MSSQL.

The first query is the one I'm struggling with. Once I get that I should have no problem generating the later ones.

Thanks for any/all help!
posted by Four Flavors at 11:05 AM on August 25, 2006

One possible way to do this is to create a temporary table with a list of ranges (datemin to datemax) you're interested in, and then do a join:

SELECT tempdates.datemin,tempdates.datemax,count(logins.loginid) FROM tempdates t LEFT JOIN logins ON logins.date>=tempdates.datemin and logins.date
Something like that.
posted by cillit bang at 11:09 AM on August 25, 2006

What you've got is close- it gives me the total number of logins across all months/days/hours by minute 1-60.

can't you just do what cerebus19 said, but datepart out the other components of the datetime you need and do a multiple group by?
posted by juv3nal at 11:23 AM on August 25, 2006

The HTML stripped swallowed my SQL:

SELECT tempdates.datemin, tempdates.datemax,count(logins.loginid) FROM tempdates t LEFT JOIN logins ON logins.date>=tempdates.datemin and logins.date<tempdates.datemax GROUP BY tempdates.datemin

You'd use a script to populate tempdates first my measuring the minimum and maximum date, then adding a row for every minute.
posted by cillit bang at 12:02 PM on August 25, 2006

cerebus19-that's what I was getting at, I suspected there was a performance implication. I don't know too much about the innards of database engines, though, so it's likely a groundless concern.
posted by evariste at 12:15 PM on August 25, 2006

juv3nal is right: You can do what I originally wrote, only add in the other parts, thus:

  SELECT
         DATEPART(mo, login_time) AS login_month,
         DATEPART(dd, login_time) AS login_day,
         DATEPART(hh, login_time) AS login_hour,
         DATEPART(mi, login_time) AS login_minute,
         COUNT(*) AS num_logins
  FROM login_tbl
  GROUP BY
         DATEPART(mo, login_time),
         DATEPART(dd, login_time),
         DATEPART(hh, login_time),
         DATEPART(mi, login_time);

If SQL Server supports grouping by aliases (I can't recall if it does or not), you can of course substitute the aliases for the DATEPART calls in the GROUP BY clause.
posted by cerebus19 at 1:05 PM on August 25, 2006

Whoops. Sorry about the formatting. It looked OK in the live preview, and I forgot to do a real preview.
posted by cerebus19 at 1:05 PM on August 25, 2006

I do this in SQL Server all the time, and I use the highly ghetto (but indisputably functional) CONVERT-and-group-by trick.

SELECT CONVERT(char(16), login_time, 20) AS login_minute, COUNT(*)
FROM logins
GROUP BY CONVERT(char(16), login_time, 20)

Now that you mention DATEDIFF, you could probably also do a DATEADD('s', DATEPART('s', login_time) * -1, login_time) to subtract the seconds from the time, leaving you with a minute figure (as long as you don't include the milliseconds in there).
posted by pocams at 1:05 PM on August 25, 2006

Four Flavors writes "to rephrase: I want to see how many logins happened per minute for every single minute ever in my first set of results.

So you want to group by whole minutes. You need a way to throw away any fractional seconds (or milliseconds).

So convert the login_time to absolute minutes since (some date), throwing away any remainder seconds.

That will do the job, but it'll display whole minutes like "minute 123456", and not a date. Purely for easier reading, you want to then convert the whole minutes back to a date. So convert that to a date, and group by on it.

In T-SQL, to convert a datetime to whole minutes, we do this: select datediff( mi,'1970-01-01', login_time ) as whole_minutes. (We've chosen '1970-01-01' arbitrarily as a "zero date").

In T-SQL to convert whole minutes back to a back, we add the whole minutes to your chosen "zero date", like this:
dateadd( mi, (whole minutes calculation) , '1970-01-01' )


In T-SQL (Sybase and MS SQL Server), the following SQL shows the actual datetime (from getdate()) and the rounded datetime:
select
getdate() as whole_milliseconds,
datediff( mi,'1970-01-01', getdate() ) as number_of_whole_minutes,
dateadd( mi, datediff( mi,'1970-01-01', getdate() ), '1970-01-01' ) as whole_minute_date

Again, the use of 1 Jan 1970 is wholly arbitrary. A better zero date would be T-SQL's own zero dates, January 1, 1753 for the datetime type and January 1, 1900 for the smalldatetime type.


Now, we just group by whole_minute_date. As cerebus19 points out grouping by an alias doesn't work with T-SQL, so the actual SQL look like this:

select
dateadd( mi, datediff( mi,'1970-01-01', login_time ), '1970-01-01' ) as whole_minute_date,
count(*)
from
logins
group by
dateadd( mi, datediff( mi,'1970-01-01', login_time ), '1970-01-01' )

Incidentally, cerebus19 correctly answered your question as you initially asked it so he should get a "best answer" check for that.
posted by orthogonality at 1:14 PM on August 25, 2006

Thanks cerebus19 & orthogonality; I ended up using yours. I tried to mark 2 as 'best' but it won't let me mark the second one.

The 'absolute minute' concept was what I was missing.
posted by Four Flavors at 1:50 PM on August 25, 2006

pocams writes "I do this in SQL Server all the time, and I use the highly ghetto (but indisputably functional) CONVERT-and-group-by trick."


Heh! Actually, when I used to work exclusively with Sybase, I did this all the time. It frankly slipped my mind this time, because I wanted to describe the essence of tat the questioner need, namely whole minutes since (some time).

But yes, this works fine; note that this is T-SQL only, and relies on all dates parts being zero padded so each date part converts to the same length string regardless of value. (In other words, if you try it outside of T-SQL, be careful!)
posted by orthogonality at 7:48 PM on August 25, 2006