[CSHARP-440] DateTime in base with milliseconds. DateTime via Query - without. Query.Eq - doesn't work with DateTime properties Created: 12/Apr/12 Updated: 02/Apr/15 Resolved: 16/Apr/12 |
|
| Status: | Closed |
| Project: | C# Driver |
| Component/s: | None |
| Affects Version/s: | 1.3.1, 1.4 |
| Fix Version/s: | 1.4.1 |
| Type: | Bug | Priority: | Critical - P2 |
| Reporter: | Brian | Assignee: | Craig Wilson |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Description |
|
When I want to store DateTime it stored like: bsonWriter.WriteString(dateTime.ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFK")); Problem: And I got empty result set, because dates doesn't match - date in base with milliseconds, generated date - without I can do only workaround about this:
Like "query Date" <= DateTime < "query Date" + 1 seconds (to handle milliseconds) |
| Comments |
| Comment by Craig Wilson [ 16/Apr/12 ] | |||||||||||||||||||||||||||
|
I agree that there are a lot of systems where second precision is enough. However, there are also a lot of systems where hour precision is enough. Context is always important when api level decisions are being made. In this case, we can't know what type of precision is required for your queries, so we go as precise as possible and let the client decide how to query it. Anyways, thanks for your bug reports. They are forcing us to think through decisions we are making more thoroughly. Please keep challenging us on things you think are important as we value your opinion and experience. | |||||||||||||||||||||||||||
| Comment by Brian [ 16/Apr/12 ] | |||||||||||||||||||||||||||
|
I don't agree with your analogy: "hours and seconds" are not the same as "seconds and milliseconds" because there is a lot of systems where seconds precision is enough. As I mentioned before - that kind of queries should be queried via "between" operator. So, my bug is invalid. | |||||||||||||||||||||||||||
| Comment by Craig Wilson [ 16/Apr/12 ] | |||||||||||||||||||||||||||
|
Brian, After reading through this exchange, it appears as though your expectations of mongodb may be different than that of the typical user. In this particular case, you are asking that an equality comparison return documents that are equal at a lower precision, something that doesn't make sense a majority of the time. Let's look at this request a little differently; instead of milliseconds and seconds, we'll use seconds and hours. You are asking that a date stored as 1:31PM be matched by an equality comparison with 1:00PM, simply because the hours are equal. I don't think there is anyway this will get implemented in the server or the driver. However, this is simple for you to do on your side. If you don't need milliseconds, then don't store them. However, the proper way to query dates is generally to use ranged queries. In the case of mongodb, since the underlying storage structure is a B-Tree, ranged queries like this are extremely fast if there is an index on the field. Let us know if you are satisfied with closing this issue. | |||||||||||||||||||||||||||
| Comment by Robert Stam [ 13/Apr/12 ] | |||||||||||||||||||||||||||
|
One of the issues involved here is that .NET DateTime values have a resolution of 100 nanosecond ticks. The BSON DateTime valus are defined to have a resolution of milliseconds. That's just the way BSON is defined. When you store a .NET DateTime in the database the value has to be converted from a .NET DateTime to a BSON DateTime, and that inevitably results in a slight loss of resolution. In the sample code I provided the two values are slightly more than a second apart (not milliseconds). If when you query by DateTime you provide the SAME value that is STORED in the database then you can easily query using Query.EQ. If you use a value that doesn't match what's stored in your database then of course you won't find a match. If you don't want milliseconds stored in the database then you should set your DateTime values to DateTimes that are rounded to the nearest second in your own code. And if you do choose to store your DateTime values in millisecond precision but only want to search with second precision then your range query is the correct way to do it. A range query should perform almost as fast as EQ, and can even use an index if one is defined. | |||||||||||||||||||||||||||
| Comment by Brian [ 13/Apr/12 ] | |||||||||||||||||||||||||||
|
Your sample is what am I talking about: You store 2 dates with a difference in milliseconds Now try to do next: {"Property" : ISODate("2012-04-12T14:31:37Z")}And you'll found nothing I just think about this - maybe it should be right that datetime can be quered only by "between" query type. But if you add some MillisecondsNull serialization option then I think it can give perfomance gain because Query.EQ() works faster then Query.And(Query.GTE(),Query.LT()) | |||||||||||||||||||||||||||
| Comment by Robert Stam [ 12/Apr/12 ] | |||||||||||||||||||||||||||
|
The line of code you refer to is only executed if you set the serialization options so that the DateTime value is serialized as a string. That is NOT the default way. The default is to serialize the DateTime value as a BSON DateTime value. Here's some code that works for me:
This code inserts two documents with different DateTime values and then uses Query.EQ to find one of them (it works, returning the first of the two documents inserted). And the output is:
If your scenario is different can you please provide some actual code to execute? | |||||||||||||||||||||||||||
| Comment by Brian [ 12/Apr/12 ] | |||||||||||||||||||||||||||
|
bsonWriter.WriteString(dateTime.ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFK")); This is not my serializer. It's from driver. I watched how standart DateTime serialization works and found this row. Query was written via Query.EQ(); I'm not Mongo expert but on my server datetime value with millisecconds is not the same as datetime without them. | |||||||||||||||||||||||||||
| Comment by Robert Stam [ 12/Apr/12 ] | |||||||||||||||||||||||||||
|
Can you provide some more information (perhaps attach a sample program demonstrating your issue)? The fact that you mention a BsonWriter makes me think that you have written a custom serializer. If so, Query.EQ won't know about your custom serializer and you will have to provide the value in the same format as that written by your custom serializer. You might also look into writing the query as a LINQ query. The LINQ implementation understands custom serializers (although you have to implement some new methods added to IBsonSerializer in 1.4 for the use of the LINQ implementation). |