Using JDBC with MongoDB to query Dates that are stored in String

Question

So, as in the title, I have the following example Document in my MongoDB database:

    {"_id":{"$oid":"5fcf541b466a3d10f55f8241"}, "dateOfBirth":"1992-11-02T12:05:17"}

As you can see, the date is stored as a String and not as an ISODate object. As far as I know, MongoDB should be able to still handle and query it as a Date. (source)

Thus, I am trying to query it in my java app with JDBC in the following way:

    java.util.Date queryDate = new GregorianCalendar(1980, Calendar.JANUARY, 1).getTime();
    Bson query = Filters.gte("dateOfBirth", queryDate);
    FindIterable<Document> result = collection.find(query);

However, this does not work. My thought process was, if I pass in a java.util.Date, then the Filters.gte() method will know i mean to query a Date and it will work as intended in MongoDB. However, I get 0 matches.

I also tried putting a formatter on my queryDate (for a different purpose, before):

    DateFormat dformat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    Bson query = Filters.gte("dateOfBirth", dformat.format(queryDate));

However, this caused the Filters.gte() to query it as Strings, according to String-Comparison, so alphabetical order roughly. This made me think initially that the original java.util.Date version did/should indeed know then, that I queried a Date and not a String, it just somehow failed to convert the one in the database to a date-type? I'm unsure how it should work.

I understand this is a niche case usage, and that I really should insert dates as ISODate in mongoDB, however in my special case right now, this is not an option.

Is there a way to query dates stored as Strings in MongoDB if I am using JDBC?

andrewJames andrewJames · Accepted Answer · 2020-12-08T19:12:54

Minor point: You are using the Java connector for MongoDB. JDBC drivers are for relational databases which use the SQL query language. I therefore changed the JDBC tag to Java in your question.

Working with Dates as Strings

Regarding the datetime format in your documents: Because of the format you are using, and because it is stored as a string, it is OK to use string comparisons when running your queries. Lexical ordering will ensure your string comparisons will be equivalent to datetime comparisons. This is what is being used by the code in the question you linked to.

Obviously this assumption will break if you have any data stored in other string formats, such as "dd-MM-yyyy", where the string ordering would not match the datetime ordering.

However you proceed, you should avoid the old and problematic Java Date and Calendar classes. Instead, use the modern java.time classes. More background here.

In your case, your documents are storing datetime data without any timezone or offset information. You can use java.time.LocalDateTime for this. The word "local" in this name actually means "no specific locality or timezone" - which matches what you have in your Mongo documents.

The Java imports:

import java.time.LocalDateTime;
import java.time.Month;
import java.time.format.DateTimeFormatter;

And an example local datetime:

LocalDateTime ldt = LocalDateTime.of(1980, Month.JANUARY, 1, 0, 0);
DateTimeFormatter dtf = DateTimeFormatter.ISO_DATE_TIME;
String s = ldt.format(dtf); // "1980-01-01T00:00:00"

Working with Dates as Objects

If you want to use a Java LocalDate object directly in your query, instead of using string comparisons, you can use a projection to create a date object in your query results, and then use the Java LocalDate object directly in your filter:

Bson resultsWithDate = Aggregates.project(Projections.fields(
        Projections.include("dateOfBirth"),
        Projections.computed("birthDate", Projections.computed("$toDate", "$dateOfBirth"))
));

The above projection adds a new dateOfBirth field to each retrieved document, and populates it via the $toDate operator.

Then we can apply our filter:

collection.aggregate(
        Arrays.asList(
                resultsWithDate,
                Aggregates.match(Filters.gte("birthDate", ldt)))
).forEach(printConsumer);

The filter now uses our ldt object, from above.

I am using the following helper method to print each results document as a JSON string in my console:

Consumer<Document> printConsumer = (final Document document) -> {
    System.out.println(document.toJson());
};

There may be a more compact or efficient way to build this MongoDB aggregate - I am not a regular Mongo user.

Also, as a final note: My use of the Mongo $toDate operator does not specify a timezone - so it defaults to Zulu time (UT timezone), as shown in the sample output below:

{
    "_id": {
        "$oid": "5fcf541b466a3d10f55f8241"
    },
    "dateOfBirth": "1992-11-02T12:05:17",
    "birthDate": {
        "$date": "1992-11-02T12:05:17Z"
    }
}

Using JDBC with MongoDB to query Dates that are stored in String

1 Answers