ordering with DateTime.UtcNow #19327

sverbach · 2024-09-24T14:33:28Z

sverbach
Sep 24, 2024

Hello everybody :)

I have a bit of a problem and wanted to ask you guys opinion (essentially if there's a easier way to achieve my goal).
This needs a bit of context, so buckle up :D

Document

So what I have is the following document, say it's an Order:

class Order
public DateTime DispatchedOn {get; set;}
public DateTime? DeliveredOn {get; set;}  // can be null,  will only be set once the stuff is delivered

And now there's a requirement to get a certain timespan, say DeliveryTime, which is calculated as follows:

DeliveryTime = (order.DeliveredOn - order.DispatchedOn) ?? (DateTime.UtcNow - order.DispatchedOn)

The DeliveryTime is not stored on the document.

Index

Since we cannot use DateTime.UtcNow in the static index definition, we just save what we can, e.g.:

class OrderIndex
public DateTime DispatchedOn {get; set;}
public DateTime? DeliveredOn {get; set;}
public TimeSpan? DispatchedUntilDelivered {get; set;}     // (DeliveredOn - DispatchedOn)

Filtering

Now there's a requirement to filter by DeliveryTime, which is not on the index.

For filtering this should be fine, given the APIs we should be able to solve this problem.
Example:
get me all items where delivery-time is greater than 5 days

would be something like

            .OpenSubclause()
            .WhereExists(x => x.DispatchedUntilDelivered)
            .AndAlso()
            .WhereGreaterThan(x => x.DispatchedUntilDelivered, TimeSpan.FromDays(5))
            .CloseSubclause()
            .OrElse()
            .OpenSubclause()
            .Not.WhereExists(x => x.DispatchedUntilDelivered)
            .AndAlso()
            .WhereLessThan(x => x.DispatchedOn, DateTime.UtcNow.AddDays(-5))
            .CloseSubclause()

Sorting

Now there's a requirement to sort by DeliveryTime, which is still not on the index.
Here's where my question is, I don't see much flexibility there in the API. We are only able to sort by one column, or am I missing something?

I would need my order logic to look something like:

=> index.DispatchedUntilDelivered == null ? (DateTime.UtcNow - index.DispatchedOn).TotalDays : index.DispatchedUntilDelivered.TotalDays

Is the only way to achieve this by using Custom Sorters? Or is there some other OrderBy API I could use?

Thank you for your time & have a beautiful day :)
Sven

Answered by ayende

Sep 25, 2024

Having said that....

Here is a trick you can pull, create a document with: configuration/current-day with the current date, update it once a day.

Then, in your index, you can do:

DeliveryTime = (order.DeliveredOn - order.DispatchedOn) ?? (LoadDocument<Configuration>("configuration/current-day").Today  - order.DispatchedOn)

What does this do?

When the package was delivered, you use the actual time.
If it isn't, you pull the current date from a document using LoadDocument

What does this matter?

You aren't computing this each and every time
When you update the configuration/current-day document - all the documents that haven't been delivered will be updated

The idea is that hopefully you …

View full answer

ayende · 2024-09-24T14:45:43Z

ayende
Sep 24, 2024
Maintainer

What does this mean order by delivery time?
Are you asking to sort by the time span?

Given:
1/ order that arrives tomorrow, but whose dispatch date is 90 days ago (so delivery time is 90 days)
2/ order that arrives next week, dispatched yesterday (delivery time is 7 days)

What should be the order?

Order (1) arrives first, but was 90 days in transit
Order (2) arrives later, but would spend less time on the road

How would you sort those?

1 reply

sverbach Sep 24, 2024
Author

whoops, clicked the wrong button, my reply is below :)
#19327 (comment)

sverbach · 2024-09-24T15:01:21Z

sverbach
Sep 24, 2024
Author

What does this mean order by delivery time?
Are you asking to sort by the time span?

yes exactly, I want to sort by the time span, more specifically by the amount of days it took to deliver.

Given:
1/ order that arrives tomorrow, but whose dispatch date is 90 days ago (so delivery time is 90 days)
2/ order that arrives next week, dispatched yesterday (delivery time is 7 days)

so, in your example it would be

orderBy deliveryTime asc => Order(2), Order(1)
orderBy deliveryTime desc => Order(1), Order(2)

it's really just about the time span, unfortunately not about the actual delivery date, that would be easy to do 😅

10 replies

ayende Sep 25, 2024
Maintainer

https://issues.hibernatingrhinos.com/issue/RavenDB-22946/Order-by-computation

This is something that we'll consider

sverbach Sep 25, 2024
Author

Sorry, that is a mistake, meant to write:

from index MyIndex
where DeliveryDuration = null and OrderDate >= $fourDaysAgo
order by OrderDate

ahh I see, yeah that could actually work, just have to see that this combination mechanism works well with paging in place.
but I think it should, will try it out today and give some feedback.

another question: since we always take maximum 100 items, do you think using a custom sorter would also be a feasible option?
we have millions of "orders" (statically indexed), but are only interested in max 100 per request, would the database then still need to compute on (potentially) every index item per request?

ayende Sep 25, 2024
Maintainer

The reason we don't already have this feature is that to sort, we have to compute over the entire dataset (all the results that matched your query).
For millions of records, that is still computing for milliions

Yes, custom sorters can help here, but they would still need to compute over the entire result set.

ayende Sep 25, 2024
Maintainer

Having said that....

Here is a trick you can pull, create a document with: configuration/current-day with the current date, update it once a day.

Then, in your index, you can do:

DeliveryTime = (order.DeliveredOn - order.DispatchedOn) ?? (LoadDocument<Configuration>("configuration/current-day").Today  - order.DispatchedOn)

What does this do?

When the package was delivered, you use the actual time.
If it isn't, you pull the current date from a document using LoadDocument

What does this matter?

You aren't computing this each and every time
When you update the configuration/current-day document - all the documents that haven't been delivered will be updated

The idea is that hopefully you have a lot less of them, and you can schedule this for an off time (midnight, for example), and then the database would re-index those

As a result, you can now easily query on DeliveryTime and sort on it.

Answer selected by sverbach

sverbach Sep 25, 2024
Author

thank you for the updates, really appreciate the quick responses!

Having said that....

Here is a trick you can pull, create a document with: configuration/current-day with the current date, update it once a day.

Then, in your index, you can do:
DeliveryTime = (order.DeliveredOn - order.DispatchedOn) ?? (LoadDocument<Configuration>("configuration/current-day").Today  - order.DispatchedOn)
What does this do?

When the package was delivered, you use the actual time. If it isn't, you pull the current date from a document using LoadDocument

What does this matter?

You aren't computing this each and every time

When you update the configuration/current-day document - all the documents that haven't been delivered will be updated

The idea is that hopefully you have a lot less of them, and you can schedule this for an off time (midnight, for example), and then the database would re-index those

As a result, you can now easily query on DeliveryTime and sort on it.

I understood correctly that we would need to create some scheduled job that updates the Configuration document on a daily basis.
or is there already something inside RavenDB that does this?

because also had this idea in mind, but wanted to make sure there isn't some kind of "Clock" mechanism inside RavenDB that would take care of that for us.

sverbach · 2024-09-27T06:44:30Z

sverbach
Sep 27, 2024
Author

Good morning,

Thank you for all of the help!

We concluded that we will be using the trick/workaround mentioned in #19327 (reply in thread), that does the trick for us :)

TLDR: we now have a current-date clock in RavenDB that we reference in our static indexes.
This way we can compute on the static index using the current date, allowing all sorts of filtering and ordering :)

Thank you very much for your support on this manner, very appreciated.

Best,
Sven

0 replies

ayende · 2024-10-10T11:13:57Z

ayende
Oct 10, 2024
Maintainer

For reference, I wrote a blog post on this:

https://ayende.com/blog/201729-B/querying-over-the-current-time-in-ravendb?key=d39c0afe76fe491181d61d3e3b73f23d

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Index

Filtering

Sorting

ordering with DateTime.UtcNow #19327

{{title}}

Replies: 4 comments 11 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Index

Filtering

Sorting

ordering with DateTime.UtcNow #19327

sverbach Sep 24, 2024

Document

Index

Filtering

Sorting

Replies: 4 comments · 11 replies

ayende Sep 24, 2024 Maintainer

sverbach Sep 24, 2024 Author

sverbach Sep 24, 2024 Author

ayende Sep 25, 2024 Maintainer

sverbach Sep 25, 2024 Author

ayende Sep 25, 2024 Maintainer

ayende Sep 25, 2024 Maintainer

sverbach Sep 25, 2024 Author

sverbach Sep 27, 2024 Author

ayende Oct 10, 2024 Maintainer

sverbach
Sep 24, 2024

Replies: 4 comments 11 replies

ayende
Sep 24, 2024
Maintainer

sverbach Sep 24, 2024
Author

sverbach
Sep 24, 2024
Author

ayende Sep 25, 2024
Maintainer

sverbach Sep 25, 2024
Author

ayende Sep 25, 2024
Maintainer

ayende Sep 25, 2024
Maintainer

sverbach Sep 25, 2024
Author

sverbach
Sep 27, 2024
Author

ayende
Oct 10, 2024
Maintainer