Time based query - records within 10 minute intervals

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
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.
 
Back
Top