CODEWITHSUNDEEP
sqlms-access
HK1
HK1

Reputation: 12210

Group By n Minutes or Hours

I need to query a table and group records by a user-defined time period that could be any integer of minutes or hours. One assumption we'll make is that any chosen time period is started at 12:00AM (if that makes any sense). In other words, if the user chooses to group records by 15 minutes, we will not allow them to say, begin grouping every 15 minutes starting at 12:07AM. We'll automatically assume/use 12:00AM as the starting point for grouping. Same for any other time period.

Do I need to create my own function for this? I'm not overly concerned about performance as I will be using other methods/limitations to try to keep performance issues at bay.

My table looks like this:

timeentry
--entryid         (autonumber)
--begindatetime   (datetime)
--enddatetime     (datetime)

If I use a function I don't think this matters but I do plan to base my groupings on begindatetime and ignore enddatetime.

I'm using MS Access but I'd like my solution to be compatible with SQL Server and MySQL if possible. However, my primary focus for the moment is just MS Access.

Upvotes: 1

Views: 1098

Answers (2)

Fionnuala
Fionnuala

Reputation: 91356

I am not quite sure what you want, but here is one idea:

SELECT DateDiff("n",CDate("00:00"),[BeginDateTime])\15 AS No15s, 
       (DateDiff("n",CDate("00:00"),[BeginDateTime])\15)*15 AS NoMins,
       Count(Table1.BeginDateTime) AS [Count]
FROM Table1
GROUP BY DateDiff("n",CDate("00:00"),[BeginDateTime])\15, 
        (DateDiff("n",CDate("00:00"),[BeginDateTime])\15)*15;

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Seems to me the Partition() function could be useful here.

Your code would create a SELECT statement based on user's choices for date (I assumed you want to limit the query to begindatetime values for a single date), time units, and group interval.

This one would be for Jun 14, 2011 as date, minutes as time units, and 15 minutes as the interval.

SELECT
    Partition(elapsed,0,1440,15) AS time_block,
    q.id,
    q.begindatetime
FROM
    [SELECT
        t.id,
        t.begindatetime,
        TimeValue(t.begindatetime) * 1440 AS elapsed
    FROM tblHK1 AS t
    WHERE
        t.begindatetime>=#2011-06-14#
        And t.begindatetime<#2011-06-15#
    ]. AS q
ORDER BY q.begindatetime;

Not sure how much you'll like this, though. Here's some sample output:

time_block id begindatetime
  60:  74   1 6/14/2011 1:06:05 AM
 555: 569   3 6/14/2011 9:15:00 AM
1395:1409   4 6/14/2011 11:15:00 PM

The time_block column isn't very user friendly.

Upvotes: 2

Related Questions