1

I need to run a query on a collection that contains a timestamp string ("YYYYMMDD:HH:MM:SS.SSSS"). I want to find all documents that have an hour less than 9 and hour greater than 14. SQL offers the MID() function but I couldn't find an equivalent function. How can I run this query in C++? If you don't know how to in C++ but in mongo shell let me know, I may be able to convert it to C++.

UPDATE Using JohnnyHK's suggestion below, I tried:

BSONObj queryafter = BSONObjBuilder().appendRegex("date", "........:0[0-8]").obj();
BSONObj queryafter = BSONObjBuilder().appendRegex("date", "........:[17-23]").obj();
c.update(dbcol, Query(querybefore), BSON("$set"<<BSON("noise"<<"true")), false, true);

It compiled but it didn't filter correctly.

postelrich
  • 3,274
  • 5
  • 38
  • 65

2 Answers2

3

It's not terribly elegant, but you could do this in the shell using a regular expression:

db.test.find({ts: /....:..:..:0[5-8]/})

In the C++ driver you would build your query object with something like:

BSONObj query = BSONObjBuilder().appendRegex("ts", "....:..:..:0[5-8]").obj();

UPDATE

For your new requirements, it gets a little more involved, but still doable:

BSONObjBuilder().appendRegex("ts", "^........:(0[0-8]|1[5-9]|2[0-3]):..:").obj();
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
  • thanks, couldn't figure out how to convert it c++ :( . Does mongo c++ have a things that lets run shell code from within a c++ program? similar system(" ") for bash – postelrich Mar 05 '13 at 02:06
  • @riotburn Not that I know of, no. I added my guess at how to build this query using the C++ driver. – JohnnyHK Mar 05 '13 at 02:29
  • I'm sorry, I meant to use less than 9am and greater than 16 (4pm). So could I use appendRegex example for [09-16] and do QUERY(not < – postelrich Mar 05 '13 at 04:43
  • tried your method, but I don't know if I used it correctly. I added an update above. Note, I corrected the format of the timestamp. – postelrich Mar 05 '13 at 05:59
  • Thanks! I tried running it but its returning results based on the seconds, not hours. – postelrich Mar 05 '13 at 20:46
  • @riotburn Sorry, with the string format change it wasn't always matching the right part of the string. See latest update. Also, read up on regular expressions so you understand what this is doing. – JohnnyHK Mar 05 '13 at 21:10
  • Thanks! It works. I had tried playing around with adding some :..:.. after but I didn't get it right. I'm guessing the . are wild cards. What does ^ signify? – postelrich Mar 05 '13 at 21:25
  • @riotburn `^` is the beginning of the string. Here's an overview: http://regexlib.com/CheatSheet.aspx – JohnnyHK Mar 05 '13 at 21:28
0

You could write your own little way to do it in c++. If you have a string "YYYY:MM:DD:HH:MM:SS.SSSS" then you can tokenise it around the : using strtok or stringstream. Then use atoi to convert the hours into an int and finally compare with <9 & >4.

Here is a strtok example and here is a stringstream example.

EDIT: If you are confident of the position of the hours in the string (Which you must be right) Then you can also do it this way without a for loop:

string timestamp = yourTimestamp;
int hours = atoi(timestamp.substr(12, 2).c_str())
if (hours < 9 && hours > 4)
    your stuff

The explanation is, substr the section that contains the hours, turn it into a c string and convert to an int. Use the hours for compare.

Community
  • 1
  • 1
Fantastic Mr Fox
  • 32,495
  • 27
  • 95
  • 175