Time based query - records within 10 minute intervals

G

Guest

I have a database with time stamped events. I need to graph or simply select
how events happened each 10 minutes in a 24 hour period. I see in a report
you do a group with an interval - is this a SQL function or the reporting
subsystem?
 
A

Allen Browne

Try typing an expression like this into the Field row in query design:
CVDate(Round([MyDateTimeField]*144,0)/144)

This relies on the fact that Date/Time fields in JET (the data engine in
Access) are stored as a special type of real number, where the integer part
represents the date and the fraction is the time expressed as part of a day
(e.g. noon 0.5, 6am = 0.25.)

There are 144 x 10-minute periods in a day. So if you multiple the number by
144, round it to zero places, and then divide by 144, it is rounded to the
nearest 10-minute period. CVDate() than converts the result back into a
date/time value (coping with nulls.)

This will not work with dates earlier than 1900, due to the special way
Access handles negative date values. It could also suffer from some rounding
issues.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top