Using date ranges in MongoDB and PHP.

I have also written similar posts relating to date ranges in Python. You can find my Question here from when I was getting frustrated and The answer to my problems here.

You seriously wouldn’t believe the trouble I’ve had in the past two weeks trying to make some progress on my Arduino, Raspberry pi, Python, PHP and MongoDB project.

Work has been very busy so the only time I’ve had to work on this is on the bus on the way to and from Dublin and from time to time very late at night.

Right, so here are a few of the problems I came up against:
The first hing I wanted to do was limit the size of my table in MongoDB. I’m collecting quite a lot of sensor data from the Arduino but I don’t need to retain this data for any more than around 2 weeks.

MongoDB allows you to create a TTL index which will delete data that was created more than a certain number of seconds ago. This is a really handy feature however, it didn’t really work for me. I think you need to store the date in BSON format and I had stored my date in ISO format because I think it will make it easier to retrieve and write the sensor entries.

Regardless, here is the code I used:

db.envirocheck.sensors.ensureIndex( { “Date”: 1 }, { expireAfterSeconds: 604800 } )

You can learn more about TTL indexes using the Fantastic MongoDB documentation that covers TTL Indexes

As I said, this didn’t work for me at all so a suggestion on Twitter that I received weeks before made me think of capped collections. These are similar to TTL indexes in that they delete old data but instead of the TTL index, this works by deleting entries that are old however it does so when the collection reaches a certain value. By writing data once a second, I find that with 500Bytes I can store just over two hours of data. I obviously need to figure out how many bytes I need for storing two weeks worth but that’s something to do when I’m feeling more awake.

The code to create a capped collection is here:

db.createCollection( “sensors”, { capped: true, size: 500000 } )

Again, look here for the MongoDB documentation for capped collections.

Next, I of course needed to set an index on my date field as I’m going to be using this to select specific temperature values for date and time ranges. That was quite straight forward.

db.sensors.ensureIndex( { “Date”: 1 } )

Next, I needed to find a way of selecting between two dates in MongoDB and PHP. You might think this is easy, but no! It’s far from it! I stupidly tried to get ahead of myself by making this really complicated. I looked at Doctrine but trust me on this, the documentation for this project is absolutely crap! Now, maybe it’s me. Maybe I’m not experienced enough to figure this out but for god sake, this documentation might seem great from a high level but unless you read it from start to finish like a book, it’s useless! there’s no context to any of their examples and huge chunks of code are missing without any pointers to the parts of the documentation that might reference them. I wasted a week reading that documentation. There’s also different variations and different versions so the whole thing is really frustrating. All I wanted to do was find data between a date or time range. I liked the simplicity of the query builder and I can really see the power of this library but the documentation really turned me off.

Finally, I came to my senses last night at about 11:30PM when I really should have been a sleep. Come to think of it, I should really be a sleep now as well but I want to get all this out of my head and on paper so to speak before I forget it. I came across This post on the MongoDB blog which made things very very clear. I had of course tried something very similar to that before I started looking for alternatives but really, it was so simple! All I was missing was converting the date into strToTime before I tried to convert it into MongoDate format. I did a lot of searching on Google but although I could find shed lodes of documentation on converting from MongoDate into PHP, I couldn’t find anything on the other way around. I obviously wasn’t looking in the right place because as soon as I saw those few letters strToTime, it all clicked.

Here’s the example from the MongoDB blog:

$start = new MongoDate(strtotime(‘1971-01-01 00:00:00’));
$end = new MongoDate(strtotime(‘1999-12-31 23:59:59’));
$collection->find(array(“create_date” => array(‘$gt’ => $start, ‘$lte’ => $end)));

This actually converts the date and time into a number like this:

1393545599

Armed with this information, I set about dynamically setting the date and time. This code will get the sensor values saved to MongoDB over the past day:

$start = new MongoDate(strtotime(date(“Y-m-d H:i:s”,”-1 days”)));
$end = new MongoDate(strtotime(date(“Y-m-d H:i:s”)));

See how easy that is? Isn’t that frustrating! I’ve spent about ten hours reading about this. Such a waste in a lot of ways but I suppose I probably learned plenty on my travels to finding out more about Mongo and the way it handles dates. Funny, in the collection, the date is stored in ISO format. For example: 2014-27-02 23:46:05. It must do some very interesting conversion back into a standard format. When I tried to check using the format that the date is stored in within the collection using (Y-m-d H:m:s) it failed to pull back any records. Maybe because MongoDate is trying to parse that from the expected strToTime number. That’s weird though because that wasn’t even working when I wasn’t using MongoDate. It’s a question I must ask on the forums when I eventually get around to creating an account.

As you can see, I’m still learning and in a lot of ways this is really frustrating. I could probably do with reading a few books on these subjects but where’s the fun in that? I rather learn as I go along.