lt() and gt() to compare dates in query, different behaviour?

Ask the community
, ,
1

Hello.
I have a collection that stores customers and the info relatad to a service plan they have. What I want to do is to query all users whose Free plans are not valid anymore (the “validUntil” field is a date before than today). This is an example of my collection:

In order to do this I have a HTTP function:

export async function get_testing(request) {
    const response = {
        "headers": {
            "Content-Type": "application/json"
        }
    };
    let email = request.query["email"];

    let today = new Date();

    const { items: freePlans } = await wixData.query('plansCollection')
        .eq("currentPlan", "Free")
        .lt("validUntil", today)  // gt() works but lt() doesn't!!!
        .find();

    if (freePlans.length > 0) {
        response.body = {
            "userEmail": "first email found " + freePlans[0].title,
            "plan": freePlans[0].currentPlan,
            "validUntil": freePlans[0].validUntil,
            "comparetodate": today,
            "usageCount": freePlans[0].usageCount
        }
    } else {
        response.body = {
            "not_found_msg": "no entries found",
            "comparetodate" : today 
        };
    }
    return ok(response);
}

So assume today is the 24/03/2023 and when I call the testing GET endpoint the query doesn’t find anything and I get:

{
    "not_found_msg": "no entries found",
    "comparetodate": "2023-03-24T20:16:43.997Z"
}

Nevertheless, just for trying, I changed lt() for gt() and indeed I get the user whose validUntil field value in the collection is a date after today:

{
    "userEmail": "first email found user2@test.com",
    "plan": "Free",
    "validUntil": "2023-04-24T20:15:06.000Z",
    "comparetodate": "2023-03-24T20:16:00.996Z",
    "usageCount": 300000
}

This does not make any sense to me as it implies that lt() and gt() don’t work the same way for dates. Am I using the lt() in an unproper way or is this a bug? Thanks in advance for all your comments!

2

I think it’s simply down to the time components in the dates. new Date () returns date AND time and your validUntil also comprises Date and Time. Your mission, should you choose to accept it, is to figure out how to compare the date components only!

3

Hi. I am not really sure about this. JavaScript date objects are, as far as my understanding goes, only an integer that represents the number of miliseconds that have passed after 01/01/1970. If this is so, then lt() and gt() are comparing only numbers so the time inside a date should be irrelevant in my case .

Any other thoughts?

4

From basic monograms on towels to intricate designs on wedding dresses, embroidery adds a touch of sophistication and elegance to any piece of clothing or accessory. best embroidery machine for patches