Hello.
I am trying to do a query on a date property but it doesn’t work. Not always.
Here is some sample code:
Initial code:
var dto = DateTimeOffset.UtcNow;
Insert document:
using (var document = new MutableDocument("id45"))
{
document.SetString("id", "id45");
document.SetDate("Timestamp2", dto);
_db.Save(document);
}
Query code:
using (var query = QueryBuilder
.Select(SelectResult.All())
.From(DataSource.Database(_db))
.Where(
Expression.Property("Timestamp2").EqualTo(Expression.Date(dto))
))
{
var results = query.Execute().ToArray();
}
This one works.
If I replace dto with dto = new DateTimeOffset(15, new TimeSpan(0)) in the above code, it still works.
The following don’t:
var dto = new DateTimeOffset(15000, new TimeSpan(0));
var dto = new DateTimeOffset(new DateTime(2018, 8, 3, 21, 54, 29).Ticks, new TimeSpan(0));
var dto = new DateTimeOffset(2018, 8, 3, 21, 54, 29, new TimeSpan(0));
What is the problem here ?
Thanks forward,
Sebastien
That’s a weird one. I am going to assume it is a bug, but if you can enable text logging in the support assembly (same one as you use for “Activate”) and change query log level to verbose, there might be some clues. I am assuming “doesn’t work” here to mean “doesn’t return results” which usually means that for some reason the query doesn’t end up matching the data. Once you do that, find an entry with JSON in it that represents the query if possible and post that.
Thank you for the reply. You are right about your assumption for “doesn’t work”: I don’t get any results while I should have.
I enabled log and probaly found the problem:
The query on new DateTimeOffset(15, new TimeSpan(0)) is translated as follow:
Query encoded as {"WHERE":["=",[".timestamp"],"0001-01-01T00:00:00.0000015+00:00"],"WHAT":[["."]]}
The query on new DateTimeOffset(15000, new TimeSpan(0)) is translated as follow:
Query encoded as {"WHERE":["=",[".timestamp"],"0001-01-01T00:00:00.0015+00:00"],"WHAT":[["."]]}
In the timestamp, .0015000 is formatted as .0015 and I guess the comparison failed on this one. However I did not find a way to check what was inserted in the database.
Here is the complete code of my test:
[TestMethod]
public void TestQueryDateTimeOffset()
{
Couchbase.Lite.Support.NetDesktop.Activate();
Couchbase.Lite.Support.NetDesktop.EnableTextLogging(@"C:\Users\sfrippiat\Desktop\CouchBaseLite");
Database.SetLogLevel(Couchbase.Lite.Logging.LogDomain.All, Couchbase.Lite.Logging.LogLevel.Verbose);
//Database.SetLogLevel(Couchbase.Lite.Logging.LogDomain.Query, Couchbase.Lite.Logging.LogLevel.Debug);
var configuration = new CouchBaseLiteSettings()
{
Bucket = "thelis-test",
Directory = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location)
};
using (var db = Provider.GetCouchBaseLiteDatabase(configuration) as CouchBaseLiteDatabase)
{
//
// Arrange
//
var dto1 = DateTimeOffset.UtcNow;
var dto2 = new DateTimeOffset(15, new TimeSpan(0));
var dto3 = new DateTimeOffset(15000, new TimeSpan(0));
//
// Act
//
DictionaryObject[] results1;
DictionaryObject[] results2;
DictionaryObject[] results3;
using (var document = new MutableDocument("TestQueryDateTimeOffset.1"))
{
document.SetDate("timestamp", dto1);
db.Db.Save(document);
}
using (var document = new MutableDocument("TestQueryDateTimeOffset.2"))
{
document.SetDate("timestamp", dto2);
db.Db.Save(document);
}
using (var document = new MutableDocument("TestQueryDateTimeOffset.3"))
{
document.SetDate("timestamp", dto3);
db.Db.Save(document);
}
using (var query = QueryBuilder
.Select(SelectResult.All())
.From(DataSource.Database(db.Db))
.Where(
Expression.Property("timestamp").EqualTo(Expression.Date(dto1))
))
{
results1 = query.Execute().Select(r => r.GetDictionary(db.Db.Name)).ToArray();
}
using (var query = QueryBuilder
.Select(SelectResult.All())
.From(DataSource.Database(db.Db))
.Where(
Expression.Property("timestamp").EqualTo(Expression.Date(dto2))
))
{
results2 = query.Execute().Select(r => r.GetDictionary(db.Db.Name)).ToArray();
}
using (var query = QueryBuilder
.Select(SelectResult.All())
.From(DataSource.Database(db.Db))
.Where(
Expression.Property("timestamp").EqualTo(Expression.Date(dto3))
))
{
results3 = query.Execute().Select(r => r.GetDictionary(db.Db.Name)).ToArray();
}
//
// Assert
//
Assert.AreEqual(1, results1.Length);
Assert.AreEqual(1, results2.Length);
Assert.AreEqual(1, results3.Length);
}
Regards,
Sebastien
Any idea ? Should I try with the source code version of the library instead of the nuget one to browse the source code ?
Haven’t had a chance to look yet. Building with source to see exactly what is going on would speed up the process though if you are willing. If you are confused about how to do it I can give some tips.
Oh by the way you can check what is stored by calling “GetString” instead of “GetDate” on the timestamp property when you retrieve the document. Internally it is stored as a string.
Thank you for those quick replies. Now I know where to look in case of issues. I changed my code to use the workaround for now.
Thanks,
Sebastien