Nearest/Closest date

[Bryan Absher]

I am storing some historical data in mongo and I was wondering if there was a way to get objects that are close to a date. 

For example:

{

                        "_id" : 1,

                        "timestamp" : ISODate("2012-10-12T20:44:34.233Z"),

                        "b" : 1,

                },

                {

                        "_id" : 2,

                        "timestamp" : ISODate("2012-10-12T20:44:39.233Z"),

                        "b" : 1,

                },

                {

                        "_id" : 3,

                        "timestamp" : ISODate("2012-10-12T20:44:44.233Z"),

                        "b" : 2,

                },

                {

                        "_id" : 4,

                        "timestamp" : ISODate("2012-10-12T20:44:49.233Z"),

                        "b" : 2,

                }

I am want to objects that have { b : 2 } and date closest to ISODate("2012-10-12T20:44:50.000Z"). I only want one result back which is _id = 4.

Is the best way just to filter the data after it is returned from open ended date range query? For example timestamp <= ISODate("2012-10-12T20:44:50.000Z").

[Jeremy Mikola]

On Friday, October 12, 2012 5:16:35 PM UTC-4, Bryan Absher wrote:

I am want to objects that have { b : 2 } and date closest to ISODate("2012-10-12T20:44:50.000Z"). I only want one result back which is _id = 4.

Is the best way just to filter the data after it is returned from open ended date range query? For example timestamp <= ISODate("2012-10-12T20:44:50.000Z").

Is it feasible for you to accomplish this with two queries? You could query with the {b: 2} criteria for both, and then each use $gte and $lte the target date, respectively, sort by the appropriate order (ascending for $gte, descending for $lte) and then limit by 1. In your application, you'd then pick the result closest to your target date.

With one query, I could imagine having a fixed threshold with an $or clause, but that's not guaranteed to match a result (depending on the threshold) and an $or essentially branches into two query paths anyway. 

[Bryan Absher]

I failed to mention that each of the records in the database have a lifespan or date range with them. I was not planning on persisting that but I was thinking that I could use a Geo Spatial index where the first coordinate is the start time and the second is the end time. 

Are there any performance implications to using non geo spatial things with that index? 

[Jeremy Mikola]

On Monday, October 15, 2012 11:45:41 AM UTC-4, Bryan Absher wrote:

I failed to mention that each of the records in the database have a lifespan or date range with them. I was not planning on persisting that but I was thinking that I could use a Geo Spatial index where the first coordinate is the start time and the second is the end time. 

Are there any performance implications to using non geo spatial things with that index?

I don't imagine there would be any performance implications vs. indexing typical coordinate data, but I've not come across anyone using 2D indexes to store non-coordinate data. Assuming the distance queries were even suitable, you would likely need to tune the bit precision of the geohash index. The default (26 bits) is intended for lat/long boundaries. Alternatively, you could scale the timestamps down to more manageable floating-point numbers.