Find which 5 minute interval. 2

[CasperTFG]

Hey Guys,

This is a real Finess question

What is the fastest way to do this. I need to find out what 5 minute interval a date/time falls under. So for example:

Given Date                Result Date
10/18/2004 12:35 PM       10/18/2004 12:35 PM
10/18/2004 12:37 PM       10/18/2004 12:35 PM
10/18/2004 12:39 PM       10/18/2004 12:35 PM
10/18/2004 12:40 PM       10/18/2004 12:40 PM
10/18/2004 12:43 PM       10/18/2004 12:40 PM

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

BTW... So far this is what I got, I think it could be faster though:

Private Function FiveMinIntvl(i_Date As Date) As Date
    Dim iSDate As Date
    
    iSDate = Format(i_Date, "MM/DD/YYYY HH:mm")
    Do Until Format(iSDate, "HHmm") / 5 = Int(Format(iSDate, "HHmm") / 5)
        iSDate = DateAdd("n", -1, iSDate)
    Loop
    FiveMinIntvl = iSDate
    
End Function

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[DrJavaJoe]

Try this:

myDateTime = "10/18/2004 12:39 PM"
Debug.Print "Result Interval: " & DateAdd("n", -(Minute(myDateTime ) Mod 5), myDateTime )

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[CasperTFG]

My routine took 7 seconds for 5000 entries, yours took 6...

And DrJavaJoe is the winner or is that weinder a star for you...

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[DrJavaJoe]

Thanks

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[MichaelRed]

It would appear that (with some convolutions) the Partion function would be a good soloution.

Since 'Database' operations are supposed to be orientated toward "SET" operations, it stands to reason that a query would generally be a better (faster) choice in developing soloutions -at least where the databse engine supports the functionallity. "Partition", with some help, appears to meet this logic. Of course, partition only supports "whole" numbers, so the DIRECT use of a date data type is not possible, however it isreasonably efficient to convert the Date type to a long, multiplying the raw data by 1440 (the number of minutes in a day) and using partition to then determine the range of each value. Of course, the partition function does need to have the min/max and group values of the ranges, so these would need (in a real world situation) to be determined from the actual data, not hand calculated and hard coded as I have done for the sample:

Use a query to gneerate the "Whole Number" ov minutes:

SELECT CLng([DtTime]*1440) AS MyTime
FROM tblDtPartition
WITH OWNERACCESS OPTION;

returns

MyTime
55121075
55121077
55121079
55121080
55121083

These values are then used in the partition query:

SELECT DISTINCTROW Partition([MyTime],55121075,55121083,5) AS Expr1, Count(qryMins2Lng.MyTime) AS Bucket
FROM qryMins2Lng
GROUP BY Partition([MyTime],55121075,55121083,5)
WITH OWNERACCESS OPTION;

which returns

Expr1	Bucket
55121075:55121079	3
55121080:55121083	2

Which, in turn, agrees with the sample data originally posted, with the exception of the 'unusual' representation of the date time values, which exercise is left for those interested enopugh to peruse the process.





MichaelRed