Hello
There is a document in which there is a date field in the form 2023-06-02T20:47:18.460+00:00 in ISO format.
Is it possible to somehow write a query so that it counts the number of documents on a specific date in which the date field goes without spaces.
For example:
2023-06-05T20:47:18.460+00:00
2023-06-04T20:47:18.460+00:00
2023-06-03T20:47:18.460+00:00
2023-06-01T20:47:18.460+00:00
The answer will be 3, since the dates go by days 5,4,3, 2 is missing, and therefore the answer is 3
It is important that the dates are in reverse order, that is, from largest to smallest
The main thing here is to take into account the year, month and day, time should not be taken into account, and if for example 2023-06-05T20:47:18.460+00:00 and 2023-06-05T19:47:18.460+00:00, the date is the same, but the time is different, then it is the same date
Using strings as dates, this could be an abomination of a query as you need to account for days in a month manually. You could turn them into proper dates and then do a window operator to check the difference between them and work from there but that’ll be a key starting point, using strings for dates is a recipe for failure.
I don’t use dates as strings. In the code they are stored as Instant, and in the database as Date
Excellent, I’ve seen it done so many time that it’s a good first point to work out if they’re being stored properly.
There was a previous topic similar to this, shall see if I can dig it out.
I would be very grateful for your help.
How much data are you dealing with? I ask as if you’re wanting to calculate this on-the-fly with 100M records every day, then running an on-demand calculation is perhaps not the best approach and a better way may be to calculate daily and keep track.
It definitely won’t be 100M, but it could be, for example, 5k. And in order not to get all 5k records for calculation with each request to the endpoint, then at the database level, find everything, calculate and return the result
I saw this on SO:
There was a question on the forums somewhere but I cannot seem to find it.
As always…test and check performance and be wary that if you calculate it on-the-fly it could be a future bottleneck, last thing you want is the logon process suddenly taking 30 seconds to work!
Yes, this is exactly what I was looking for. Thank you very much for your help!