[CSHARP-4929] Linq3Implementation: unknown operator: $dateTrunc Created: 10/Jan/24  Updated: 11/Jan/24  Resolved: 11/Jan/24

Status: Closed
Project: C# Driver
Component/s: API
Affects Version/s: 2.23.1
Fix Version/s: None

Type: Bug Priority: Unknown
Reporter: Sasan Pasha Assignee: Robert Stam
Resolution: Works as Designed Votes: 0
Labels: Bug
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Documentation Changes Summary:

1. What would you like to communicate to the user about this feature?
2. Would you like the user to see examples of the syntax and/or executable code and its output?
3. Which versions of the driver/connector does this apply to?


 Description   

Summary

When you want to do specific date comparisons, the query fails with the following error:

// MongoDB.Driver.MongoCommandException: Command aggregate failed: unknown operator: $dateTrunc.
  at MongoDB.Driver.Core.WireProtocol.CommandUsingCommandMessageWireProtocol`1[TCommandResult].ProcessResponse (MongoDB.Driver.Core.Connections.ConnectionId connectionId, MongoDB.Driver.Core.WireProtocol.Messages.CommandMessage responseMessage) [0x001e7] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.WireProtocol.CommandUsingCommandMessageWireProtocol`1[TCommandResult].ExecuteAsync (MongoDB.Driver.Core.Connections.IConnection connection, System.Threading.CancellationToken cancellationToken) [0x00310] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.Servers.Server+ServerChannel.ExecuteProtocolAsync[TResult] (MongoDB.Driver.Core.WireProtocol.IWireProtocol`1[TResult] protocol, MongoDB.Driver.Core.Bindings.ICoreSession session, System.Threading.CancellationToken cancellationToken) [0x000ad] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.Operations.RetryableReadOperationExecutor.ExecuteAsync[TResult] (MongoDB.Driver.Core.Operations.IRetryableReadOperation`1[TResult] operation, MongoDB.Driver.Core.Operations.RetryableReadContext context, System.Threading.CancellationToken cancellationToken) [0x0012d] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.Operations.ReadCommandOperation`1[TCommandResult].ExecuteAsync (MongoDB.Driver.Core.Operations.RetryableReadContext context, System.Threading.CancellationToken cancellationToken) [0x00098] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.Operations.AggregateOperation`1[TResult].ExecuteAsync (MongoDB.Driver.Core.Operations.RetryableReadContext context, System.Threading.CancellationToken cancellationToken) [0x000af] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.Core.Operations.AggregateOperation`1[TResult].ExecuteAsync (MongoDB.Driver.Core.Bindings.IReadBinding binding, System.Threading.CancellationToken cancellationToken) [0x0012d] in <cd36eac244264074bbff0d6b85d1b81a>:0 
  at MongoDB.Driver.OperationExecutor.ExecuteReadOperationAsync[TResult] (MongoDB.Driver.Core.Bindings.IReadBinding binding, MongoDB.Driver.Core.Operations.IReadOperation`1[TResult] operation, System.Threading.CancellationToken cancellationToken) [0x00075] in <1f4ccf81a3794552bec7798e120d9a2b>:0 
  at MongoDB.Driver.MongoCollectionImpl`1[TDocument].ExecuteReadOperationAsync[TResult] (MongoDB.Driver.IClientSessionHandle session, MongoDB.Driver.Core.Operations.IReadOperation`1[TResult] operation, MongoDB.Driver.ReadPreference readPreference, System.Threading.CancellationToken cancellationToken) [0x0009f] in <1f4ccf81a3794552bec7798e120d9a2b>:0 
  at MongoDB.Driver.MongoCollectionImpl`1[TDocument].AggregateAsync[TResult] (MongoDB.Driver.IClientSessionHandle session, MongoDB.Driver.PipelineDefinition`2[TInput,TOutput] pipeline, MongoDB.Driver.AggregateOptions options, System.Threading.CancellationToken cancellationToken) [0x002e3] in <1f4ccf81a3794552bec7798e120d9a2b>:0 
  at MongoDB.Driver.MongoCollectionImpl`1[TDocument].UsingImplicitSessionAsync[TResult] (System.Func`2[T,TResult] funcAsync, System.Threading.CancellationToken cancellationToken) [0x000fe] in <1f4ccf81a3794552bec7798e120d9a2b>:0 
  at MongoDB.Driver.Linq.Linq3Implementation.Translators.ExpressionToExecutableQueryTranslators.ExecutableQuery`3[TDocument,TOutput,TResult].ExecuteAsync (MongoDB.Driver.IClientSessionHandle session, System.Threading.CancellationToken cancellationToken) [0x001d7] in <1f4ccf81a3794552bec7798e120d9a2b>:0  

How to Reproduce

            var interactions = await (await EquipmentInteractionsCollection.AsQueryable()
                .Where(b => b.MemberId == memberId &&
                            (b != null && b.InteractionDate != null && b.InteractionDate.Value.Date == beginDate.Date)).ToCursorAsync()).ToListAsync(); 

Additional Background

Please provide any additional background information that may be helpful in diagnosing the bug.



 Comments   
Comment by Sasan Pasha [ 11/Jan/24 ]

hi robert@mongodb.com  - thanks. let's close this ticket. I am hopeful that the Azure Cosmo team will push version 5 out.

Comment by Robert Stam [ 10/Jan/24 ]

Using .NET local times is dangerous because it depends on the time zone of the machine the code is running on.

But assuming you have the machine timezone right that looks like the right way to get the beginning midnight value. Then add 24 hours to it to get the ending midnight value.

Note that if you use `.Date` (assuming you upgrade to a server version that supports it) when that executes on the server it would be comparing the `Date` in the UTC timezone, not some local time zone. So the range comparison might be the only way to write your query anyway.

Comment by Sasan Pasha [ 10/Jan/24 ]

so something like this should work: 

var startDateLocatTime = DateTime.SpecifyKind(DateTime.ParseExact("2024-01-10 00:00:00.000", "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture), DateTimeKind.Local);
 
var beginDateStartingMidnight =   startDateLocatTime.ToUniversalTime();

Comment by Robert Stam [ 10/Jan/24 ]

You could try comparing ranges. For example, replace

 b.InteractionDate.Value.Date == beginDate.Date

with

b.InteractionDate.Value >= beginDateStartingMidnight && b.InteractionDate.Value < beginDateEndingMidnight

Make sure that all dates are stored in UTC in the database (MongoDB requires that) and that your comparison values are in UTC also.

Comment by Sasan Pasha [ 10/Jan/24 ]

this is for the latest server version 4.2 on Azure CosmoDB - if this only works for Server Version 5, do you know a way to re-write the query so it works for 4.2?

Comment by Robert Stam [ 10/Jan/24 ]

What server version are you using?

Note that the `$dateTrunc` operator was added in server version 5.0.

If you are using an older server version then you can't use this functionality.

Comment by Sasan Pasha [ 10/Jan/24 ]

The same applies to Date.Year, Date.Month, Date.Day comparisons. 

Comment by PM Bot [ 10/Jan/24 ]

Hi sasan.pasha@gmail.com, thank you for reporting this issue! The team will look into it and get back to you soon.

Generated at Wed Feb 07 21:49:48 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.