Lucene query on dates

[Trevor Townsend]

Hi,

I have this object in my db (RavenDB version 2330).

{

  "Schedule": "2013-03-22T14:00:00.0000000-04:00"

}

This query returns the record: 

var offset = new TimeSpan(-4, 0, 0);

var dtn = new DateTimeOffset(2013, 3, 22, 14, 0, 0, offset);

var test2 = session.Query<MySchedule>().Where(x => x.Schedule == dtn).ToList();

And so does this query:

var someOffset = new TimeSpan(-10, 0, 0);

var dto = new DateTimeOffset(2013, 3, 22, 8, 0, 0, someOffset);

var test3 = session.Query<MySchedule>().Where(x => x.Schedule == dto).ToList();

However, this Lucene query returns nothing. Soooo, what am I doing wrong this time? :)

var aDate = new DateTime(2013, 3, 22, 18, 0, 0);

var aDateString = DateTools.DateToString(aDate, DateTools.Resolution.MILLISECOND);

var test1 = session.Advanced.LuceneQuery<MySchedule>().Where("Schedule:" + aDateString).ToList();

//Schedule:20130322180000000 - also doesn't select in raven studio.

  

I can put a failing test up if anyone's interested.

thanks,

Trevor.

[Matt Johnson]

Hmmm. I'm not sure what your DateTools.DateToString method is doing, but I can probably explain your results.

In RavenDB - everything is a string.  Dates are very interesting strings, but we still have to treat them as strings, and that means standardizing on a format.  Raven's format is ISO8601, with fractional seconds to 7 decimal places - which is the same thing you get with .ToString("o") from a DateTime or DateTimeOffset.

Basically, you can't use the format you showed of 20130322180000000 You would instead need the full 2013-03-22T18:00:00.0000000Z in order to get the correct results.

Also note that DateTimeOffsets are stored as provided, but are indexed as their UTC DateTime equivalents.  This is so you can do proper range filtering and sorting.  You need the 7 digits of precision and the Z specifier.  If you leave them off, you might get close matches for > or <, but you will be missing exact matches for ==, <=, and >=.

If you use the RavenDB LINQ api, all of the translation is done for you - so you can just use the DateTime or DateTimeOffset and not worry about how things are indexed.  But if you are writing a Lucene query, the simplest way to get the correct date string is yourDateTimeOffset.ToString("o")

If for some reason you are working with DateTime with either Local or Unspecified kinds, then the values are just indexed as-is.  But you really shouldn't be indexing dates like these anyway because the results could be ambiguous.

[Matt Johnson]

Correction, I meant to say:  yourDateTimeOffset.UtcDateTime.ToString("o")

[Trevor Townsend]

Thanks for your reply Matt.  Your suggestion of using "2013-03-22T18:00:00.0000000Z" as the correct format did the trick.  

However, the "20130322180000000" Lucene query is based on the example found (at line 35) at the following link, and it used to work in build 960, with Raven.Client 1: https://github.com/ravendb/ravendb/commit/721c50ea51ff7721928cca76de957e9f7d9e3786

This code is linked as working examples for Lucene date queries from the RavenDB lucene query documentation here:http://ravendb.net/docs/2.0/faq/lucene-queries-examples   I guess the documentation is out of date. 

I agree, the Raven LINQ api makes it all much, much nicer, especially with the indexing as UTC-0 and the automatic conversion of the DateTimeOffsets in the query to UTC-0.  Back in v960 and client 1 I couldn't get the linq api to work for me with DateTimeOffsets.  Now that I'm on 2330 and client 2, I'm using the Linq api and it's great.  The fact that the Lucene query wasn't working was bugging me though.

Also, DateTools is Raven.Abstractions.Linq.DateTools. 

thanks again!

t.

[Oren Eini (Ayende Rahien)]

This commit is from 3 years ago, and the date format was changed between 1.0 and 2.0

Note that you really shouldn't be doing this:

Where("Schedule:" + aDateString)

Instead, you need to do:

WhereEquals("Schedule", dateVal)

And it will take care of formatting for you

--
You received this message because you are subscribed to the Google Groups "ravendb" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.