How can I use a range of times to select DataMapper entries?

I have a DataMapper based SQLite database. I store the time at which the data upon which the model is built as Msrun.rawtime or a property :rawtime, DateTime, of the Model Msrun.

What I need to be able to do is select a date/time range in a filter, and then sort the DataMapper entries according to that time filter. Like this:

Msrun.all.size # => 63
matches = Msrun.all( begintime: 2010-11-03T21:33:00-0600, endtime: 2011-04-09T23:59:59-0600 )
matches.size # => 12

As my database has ~500 properties between this and submodels, and I expect to generate ~100 of these entries per month, I would like something that is really fast too. Is that going to require SQL? Is this possible? Am I making this harder than it sh开发者_高级运维ould be/is there an easier way to configure my data to enable this type of sort?

I'm not sure what is you want to do? If you want to query for items that happen between a particular start and end time, you can use:

Mrsun.all(:rawtime => start_time..end_time)

This will generate SQL something like

SELECT ... FROM msruns WHERE rawtime > start_time AND rawtime < end_time;

Does that answer your question?

A fuller example:

require 'rubygems'
require 'dm-core'
require 'dm-migrations'


# setup the logger
DataMapper::Logger.new($stdout, :debug)

# connect to the DB
DataMapper.setup(:default, 'sqlite3::memory:')

class Msrun
  include DataMapper::Resource

  # properties
  property :id, Serial
  property :rawtime, DateTime

end

DataMapper.finalize.auto_migrate!


10.times do |n|
  Msrun.create(:rawtime => DateTime.new(2011, 1, 1, 0, 0 , n))
end

p Msrun.all(:rawtime => DateTime.parse('2011-1-1T00:00:04+0100')..DateTime.parse('2011-1-1T00:00:07+0100'))