Ticks search in RQL

[Rune Vistnes]

Indexing a DateTime value using ticks results in faster queries. In C#, it is easy to convert a DateTime to ticks when necessary. However, when we want to query the index from RavenDB Studio, we have to convert a date to ticks manually.

I checked the QueryMethod method to see if there's any build in function to convert a DateTime to ticks in RQL, but there doesn't seem to be one.

What is the preferred way to do this?

For example, I would want to do something like this:

from index 'Foo/Bar'

where Time > ticks('2020-01-01T00:00:00.0000000Z')

[Igal Merhavia]

Hi,

You cannot convert string to DateTime object or ticks in the filtration (WHERE clause).

In a regular scenario when you use client code you will get the ticks before calling the query (and send them as a parameter to the query for instance).

If you just want to test your Index in the studio you can do something like this:

declare function func(currentTicks, strDate) {
    var date = new Date('2020-01-01T00:00:00.0000000Z');
    var epochTicks = 621355968000000000;
    var ticksPerMillisecond = 10000;
    var ticks = epochTicks + (date.getTime() * ticksPerMillisecond);
    return ticks > currentTicks;
}
from Orders as o
select func(o.OrderedAt.Ticks, '1996-07-06T00:00:00.0000000') as IsBigger

** Example for Sample Data **

 

from index 'Foo/Bar' as o
select func(o.Time, '2020-01-01T00:00:00.0000000Z') as IsBigger

** Example for your scenario ** 

In the SELECT clause, you can use your own JavaScript function.

JavaScript let you much more flexibility.

You cannot use JavaScript function in the WHERE clause.

This will not filter your data but will display you if the ticks of the document is bigger or not.

Best regards,

Igal

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/afcdb18c-e8a3-459c-a420-36628233e101%40googlegroups.com.

[Rune Vistnes]

Thanks. Sorry to hear that it's not possible to do in the where clause.

Would it be hard to add support for that at a later time? A tick conversion from a date string could still be performed before sending the query to Lucene, so it should be feasible.

On Thursday, February 13, 2020 at 2:20:40 PM UTC+1, Igal Merhavia wrote:

Hi,

You cannot convert string to DateTime object or ticks in the filtration (WHERE clause).

In a regular scenario when you use client code you will get the ticks before calling the query (and send them as a parameter to the query for instance).

If you just want to test your Index in the studio you can do something like this:

declare function func(currentTicks, strDate) {
    var date = new Date('2020-01-01T00:00:00.0000000Z');
    var epochTicks = 621355968000000000;
    var ticksPerMillisecond = 10000;
    var ticks = epochTicks + (date.getTime() * ticksPerMillisecond);
    return ticks > currentTicks;
}
from Orders as o
select func(o.OrderedAt.Ticks, '1996-07-06T00:00:00.0000000') as IsBigger

** Example for Sample Data **

 

from index 'Foo/Bar' as o
select func(o.Time, '2020-01-01T00:00:00.0000000Z') as IsBigger

** Example for your scenario ** 

In the SELECT clause, you can use your own JavaScript function.

JavaScript let you much more flexibility.

You cannot use JavaScript function in the WHERE clause.

This will not filter your data but will display you if the ticks of the document is bigger or not.

Best regards,

Igal

On Thu, Feb 13, 2020 at 10:37 AM Rune Vistnes <rune....@gmail.com> wrote:

Indexing a DateTime value using ticks results in faster queries. In C#, it is easy to convert a DateTime to ticks when necessary. However, when we want to query the index from RavenDB Studio, we have to convert a date to ticks manually.

I checked the QueryMethod method to see if there's any build in function to convert a DateTime to ticks in RQL, but there doesn't seem to be one.

What is the preferred way to do this?

For example, I would want to do something like this:

from index 'Foo/Bar'

where Time > ticks('2020-01-01T00:00:00.0000000Z')

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rav...@googlegroups.com.

[Oren Eini (Ayende Rahien)]

The typical way you'll do that is when you send the query. Are you asking about manually running those queries from the studio?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/8c22cd58-52ee-40b7-8d6c-fc3279dc41ea%40googlegroups.com.

--

Oren Eini CEO   /   Hibernating Rhinos LTD Mobile:  +972-52-548-6969 Sales:  sa...@ravendb.net Skype:  ayenderahien Support:  sup...@ravendb.net

[Rune Vistnes]

Yes.

Some support tickets require us to manually look for documents among millions. Every document is timestamped. For performance reasons, we index the timestamp using ticks. When we query the index from our product, we convert the date to ticks before we send the query. When we have to manually do some investigation from studio, we either have to manually convert to ticks, or we query the collection directly using date strings, which of course leads to a dynamic index to be created.

What would be desired is the option to let studio convert a date string to ticks before sending it to the server, or for the server to convert it before sending it to lucene.

You received this message because you are subscribed to a topic in the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ravendb/rwMzQ96-zL4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ravendb+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/CAF0G-Zh5TJh%2BMAGiomEw_PCGieWdBPVMRi9pD9HO1vZbG7kD-Q%40mail.gmail.com.

--

Vennlig hilsen,
Rune

[Oren Eini (Ayende Rahien)]

In the studio, you can use:

$ticks  = new Date("2020-01-01T00:00:00.0000000Z").getTime()

from index 'Foo/Bar'

where Time > $ticks

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/CAHciVsLO0Z-a1RkRHxGcwyrbcMNDUQRw%3DjW9oBKsdkoWEvdv_A%40mail.gmail.com.

[Oren Eini (Ayende Rahien)]

Actually, need this:

((d.getTime() * 10000) + 621355968000000000)

To get the right ticks