If space is not an issue to store additional denormalized fields and you are at the design part of the application (so you don't have to come back and modify existing data), you could:
- Store the start-hour and end-hour as additional attributes to keep the hour/minutes.
- Apply composite indices on start_time, start_hour and end_time, end_hour so your search performance is good.
- Run a query on all four fields.
See sample document:
{
_id: "4e9650ed1d41c811bc000000",
bid: 31.0,
start_time: 2011-04-04 09:30:00 UTC,
start_hour: "09:30:00", // new field
volume: 31.0,
end_time: 2011-04-04 09:59:59 UTC,
end_hour: "09:59:59", // new field
ask: 0.0 ,
price 1329.75
}
It seems that the answer to your questions is that you cannot separate the components of the date and time efficiently in your query and for example take advantage of indices to speed up queries.
My suggestion above requires changing your schema slightly to achieve what you need. It seems that it would pay off greatly for the sacrifice of a few bytes extra on each document and the minor code changes.
If you have existing data, you could run a Map/Reduce to separate the components into a new collection and use that new collection going forward.
I hope this helps.
