Issue with a SQL query on a date time field in CosmosDB

Question

I have to make a query that selects the elements of a collection that belong to an interval between two dates, start and end. While the startdate constraint is respected, the enddate one always seems to be two hours behind. The items extracted are greater or equal to startdate and are less than or equal to enddate minus two hours.

An example of an item is the following:

{
  "messageUID": "xxxxxxxxx22019-05-31T16:59:16+02:00",
  "deviceId": "xxxxxxxxxxxxx",
  "dateTimeDevice": "2019-05-31T14:59:16",
  "messageId": 39,
  "release": 104,
  "VIn": 23430,
  "VOut": 20592,
  "AIn": 53,
  "AOut": 40,
  "CosPhi": 43,
  "W": 54,
  "Var": 112,
  "VA": 125,
  "WhCnt": 365009,
  "VarhCnt": 756729,
  "TimeSlot": 0,
  "MeterTS": "2019-05-31 16:59:17",
  "Sampling": 60,
  "Wh": 3422,
  "Varh": 7098,
  "WSaved": 0,
  "EventProcessedUtcTime": "2019-05-31T14:59:18.3140933Z",
  "PartitionId": 1,
  "EventEnqueuedUtcTime": "2019-05-31T14:59:18.29Z",
  "IoTHub": {
    "MessageId": null,
    "CorrelationId": null,
    "ConnectionDeviceId": "Device",
    "ConnectionDeviceGenerationId": "636909297614425839",
    "EnqueuedTime": "2019-05-31T14:59:18.296Z",
    "StreamId": null
  },
  "id": "EBBBrain-1060894172",
  "_rid": "dEkOAONukRECAAAAAAAAAA==",
  "_self": 
  "dbs/dEkOAA==/colls/dEkOAONukRE=/docs/dEkOAONukRECAAAAAAAAAA==/",
  "_etag": "\"2400a2a2-0000-0c00-0000-5cf1415c0000\"",
  "_attachments": "attachments/",
  "_ts": 1559314780
}

The where clause of the query is as follows:

sqlQuerySpec.QueryText += "where (c.EventProcessedUtcTime >= @startdate) and ";
sqlQuerySpec.QueryText += "(c.EventProcessedUtcTime <= @enddate) and ";
sqlQuerySpec.QueryText += "(c.deviceId = @deviceId)";
sqlQuerySpec.Parameters = new SqlParameterCollection()
  {
    new SqlParameter("@startdate",startdate.ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFZ")),
    new SqlParameter("@enddate",enddate.ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFZ")), 
    new SqlParameter("@deviceId", deviceId)
  };

In debug it seems that the parameters are passed correctly.

I solved the issue changing the code as follows :

sqlQuerySpec.QueryText += "where (RIGHT(c.messageUID, 25) >= @startdate) and ";
sqlQuerySpec.QueryText += "(RIGHT(c.messageUID, 25) <= @enddate) and ";
sqlQuerySpec.QueryText += "(c.deviceId = @deviceId)";
sqlQuerySpec.Parameters = new SqlParameterCollection()
  {
    new SqlParameter("@startdate",DateTime.SpecifyKind(startdate,DateTimeKind.Unspecified)),
    new SqlParameter("@enddate",DateTime.SpecifyKind(enddate,DateTimeKind.Unspecified)), 
    new SqlParameter("@deviceId", deviceId)
  };

Thanks in advance to all.

It is properly a daylight saving time issue. Are you startdate and enddate into same local timezone? — Frank Nielsen
Yes, they are. For example startdate.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFZ") => "2019-08-03T00:28:43Z" enddate.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.FFFFFFFZ") => "2019-08-04T00:28:43Z" — Simone Spagna
@Frank: Sorry, I tried applying the time zone without success. I only have the problem at the top end of the query. I have also tried imposing static values on startdate and enddate but the problem still occurs. The range of returned items ranges from startdate to enddate-2 hours. If it were a time zone problem it would be applied to both startdate and enddate. — Simone Spagna
Have you tried using the native value/datetime object instead of a string value? — Frank Nielsen

Simone Spagna Simone Spagna · Accepted Answer · 2019-08-13T06:08:12

I solved my issue with the following code :

sqlQuerySpec.QueryText += "where (RIGHT(c.messageUID, 25) >= @startdate) and ";
sqlQuerySpec.QueryText += "(RIGHT(c.messageUID, 25) <= @enddate) and ";
sqlQuerySpec.QueryText += "(c.deviceId = @deviceId)";
sqlQuerySpec.Parameters = new SqlParameterCollection()
  {
    new SqlParameter("@startdate",DateTime.SpecifyKind(startdate,DateTimeKind.Unspecified)),
    new SqlParameter("@enddate",DateTime.SpecifyKind(enddate,DateTimeKind.Unspecified)), 
    new SqlParameter("@deviceId", deviceId)
 ;

Thanks to all.

Issue with a SQL query on a date time field in CosmosDB

1 Answers