Querying records using date and time

[mattkhaw]

@ujjwalguptaofficial I'm trying to query records using not only date but with time as well. AFAIK, I've tried querying using date only and it works. However if time is introduced into the mix, the query failed to retrieve any records. I know I did ask this before on Gitter but I just didn't have the time to research on this further and just use a workaround using array filters.

This can be simulated with the example over here.

Date and time (Doesn't work) -> Link

select({
    from: "Orders",
    where: {
        orderDate: {
            in: [new Date('1996-07-04 00:00:00'), new Date('1997-01-14 00:00:00')]
        }
    }
});

Date only (Works) -> Link

select({
    from: "Orders",
    where: {
        orderDate: {
            in: [new Date('1996-07-04'), new Date('1997-01-14')]
        }
    }
});

The example is using the IN operator. However equals also don't work with the same criteria. Is there a solution for this scenario? The reason why I need to use either IN or equals is because I need to get specific dates at specific times hence range operators are not applicable here. I'm currently using version 4.4.7.

[ujjwalguptaofficial]

Ok, will take a look

[ujjwalguptaofficial]

@mattkhaw works perfectly for me - in the query you are specifying value which does not exist in record, so you are getting 0 records. Take a look at below link -

https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20%22Orders%22%2C%0A%20%20%20%20where%3A%20%7B%0A%20%20%20%20%20%20%20%20orderDate%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20in%3A%20%5Bnew%20Date(%221996-07-04T00%3A00%3A00.000Z%22)%2C%20new%20Date(%221996-07-05T00%3A00%3A00.000Z%22)%5D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D)%3B

[mattkhaw]

I see. Have to include the proper timezone as well. I’ve tried using moment as well to convert to JavaScript Date object as well but it doesn’t work too. The moment Date object seems to be correct as well. I wonder what’s going on there.

…

________________________________ From: Ujjwal Gupta ***@***.***> Sent: Friday, November 18, 2022 12:42 PM To: ujjwalguptaofficial/JsStore ***@***.***> Cc: Matthew Khaw ***@***.***>; Mention ***@***.***> Subject: Re: [ujjwalguptaofficial/JsStore] Querying records using date and time (Discussion #302) @mattkhaw<https://github.com/mattkhaw> works perfectly for me - in the query you are specifying value which does not exist in record, so you are getting 0 records. Take a look at below link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20%22Orders%22%2C%0A%20%20%20%20where%3A%20%7B%0A%20%20%20%20%20%20%20%20orderDate%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20in%3A%20%5Bnew%20Date(%221996-07-04T00%3A00%3A00.000Z%22)%2C%20new%20Date(%221996-07-05T00%3A00%3A00.000Z%22)%5D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D)%3B — Reply to this email directly, view it on GitHub<#302 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ADEKOJ7WS4ATAG4VQGN7XNTWI4CKDANCNFSM6AAAAAASDFKB4A>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[ujjwalguptaofficial]

I am sure it must be something small, can you give me something reproducable ?

[mattkhaw]

I'm still checking on this. Halfway making an example, I noticed that the code I've shared here works. It might be something else, maybe milliseconds. The reason why it might be that case is because the date and time up till seconds matches the output from moment. It might be caused by that. I'll update here when I get more info.

[ujjwalguptaofficial]

You need exact time to make equal operation work, better to use timestamp. Otherwise i would suggest to use less than greater than operator.

[mattkhaw]

Yes, I'm aware of that and I've found the problem. The milliseconds is causing the issue. I've tried eliminating the milliseconds entirely during my testing and the code works fine.

For this case, I have to use equals instead of the greater/less than operators because it is time sensitive. Since I know what's the problem now, it's not difficult to fix. At the very least, now I know it is not an issue with JsStore. That's all I want to know.