Extracting Date and Time info from a string.

Question

I have a database full of data, including a date and time string, e.g. Tue, 21 Sep 2010 14:16:17 +0000

What I would like to be able to do is extract various documents (records) from the database based on the time contained within the date string, Tue, 21 Sep 2010 14:16:17 +0000.

From the above date string, how would I use python and regex to extract documents that have the time 15:00:00? I'm using MongoDB by the way, in conjunction with Python.

Answer 1

I don't know MongoDB, but shouldn't something like this work?

SELECT * FROM Database WHERE Date LIKE '%15:00:00%'

If you have a date string, the only place it contains colons will be the time part of the date, so that should be good enough without a regex. It would be better, of course, if you had an actual timestamp instead of a string in your date field.

Answer 2

You can use $where:

db.collection.find({$where: "var d = new Date(this.dateProperty); return d.getUTCHours() == 15 && d.getUTCMinutes() == 0 && d.getUTCSeconds() == 0"})

Or regular expression:

db.collection.find({dateProperty: /.*15:00.*/})

The second can be a bit faster than first but both will be relatively slow. To speedup things you would store dates in built-in date format. Also if you need to query on datetime components consider adding indexable date representation such as {y:2010,m:9,d:21,h:14,i:16,s:17} (properties depend on your query needs, if you only need to query by hour you would have {h:14}). Then you can have index per each component.