mongodb: query across a date range

Question

Using the mongocxx driver, I need to query mongodb for documents (of stock data) that fall within a certain date range.

Consider the following document format:

{
  date : ISODate("2010-01-01T00:00:00Z"),
  open : 12.00,
  high : 13.00,
  low : 11.00,
  close : 12.50,
  volume : 100000
}

Say I have one collection per stock, and hundreds of these documents per collection, each with a different date.

If a user supplies two dates formatted as strings (yyyy-mm-dd):

std::string start_date = "2010-01-01";
std::string end_date = "2010-02-05";

How can I query mongo to get all the files with dates between "start_date" and "end_date", (inclusive)?

Note: I am using mongodb 3.2.12, mongocxx driver version 3.0.2

Thanks,

score 0 · Accepted Answer · answered Feb 13 '17 at 19:33

Unfortunately, there doesn't seem to be a way to parse dates from strings with arbitrary timezones; all dates parse are assumed to be in the user's locale, which means you'll need to provide an offset to be able to correctly query the UTC dates stored in the database. Ideally these could be generated when the user provides a string, but this will obviously depend on the nature of your application.

Once you have the offset and the date string, std::get_time will get you most of the way there. After that, you just need to convert the std::tm to a type that you can construct a bsoncxx::types::b_date from and then query as usual. Here's some sample code that does the job:

#include <chrono>
#include <cstdint>
#include <ctime>
#include <iomanip>
#include <iostream>
#include <ostream>
#include <sstream>
#include <string>

#include <bsoncxx/builder/basic/document.hpp>
#include <bsoncxx/builder/basic/kvp.hpp>
#include <bsoncxx/builder/basic/sub_document.hpp>
#include <bsoncxx/json.hpp>
#include <bsoncxx/types.hpp>
#include <mongocxx/client.hpp>
#include <mongocxx/uri.hpp>

bsoncxx::types::b_date read_date(const std::string& date,
                                 std::int32_t offset_from_utc) {
    std::tm utc_tm{};
    std::istringstream ss{date};

    // Read time into std::tm.
    ss >> std::get_time(&utc_tm, "%Y-%m-%d");

    // Convert std::tm to std::time_t.
    std::time_t utc_time = std::mktime(&utc_tm);

    // Convert std::time_t std::chrono::systemclock::time_point.
    std::chrono::system_clock::time_point time_point =
        std::chrono::system_clock::from_time_t(utc_time);

    return bsoncxx::types::b_date{time_point +
                                  std::chrono::hours{offset_from_utc}};
}

int main() {
    // User inputs

    std::string start_date = "2010-01-01";
    std::string end_date = "2010-02-05";

    std::int32_t offset_from_utc = -5;

    // Code to execute query

    mongocxx::client client{mongocxx::uri{}};
    mongocxx::collection coll = client["db_name"]["coll_name"];

    bsoncxx::builder::basic::document filter;
    filter.append(bsoncxx::builder::basic::kvp(
        "date", [start_date, end_date,
                 offset_from_utc](bsoncxx::builder::basic::sub_document sd) {
            sd.append(bsoncxx::builder::basic::kvp(
                "$gte", read_date(start_date, offset_from_utc)));
            sd.append(bsoncxx::builder::basic::kvp(
                "$lte", read_date(end_date, offset_from_utc)));
        }));

    for (auto&& result : coll.find(filter.view())) {
        std::cout << bsoncxx::to_json(result) << std::endl;
    }
}

In the time since I posted the question, I found [this](http://stackoverflow.com/questions/21021388/how-to-parse-a-date-string-into-a-c11-stdchrono-time-point-or-similar) post which helped with the first part. However, my queries weren't working because I didn't account for UTC time, so thanks for pointing that out! — , Feb 14 '17 at 15:52

mongodb: query across a date range

1 Answers1