Azure Document DB Stored procedure DateTime query

Question

I am using Document DB stored procedure to retrieve telemetry time based data into my C# web application. I am having 1000 of documents in my collection. I want to do calculations based on Date Time object. I want to get all documents based on time interval. Using SQL query i am getting result of last 24 hrs records.

SqlQuerySpec query = new SqlQuerySpec("SELECT * FROM c where (c.logdatetime between '" + DateTime.Now.AddDays(-1).ToString("yyyy-MM-ddTHH:mm:ssZ") + "' and '" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ssZ") + "')");

I want to write same query using Stored Procedure. How to do it in Stored Procedure. Thanks in advance.

score 1 · Answer 1 · answered Dec 12 '17 at 21:27

First of all: I would advise you against performing your queries against a formatted DateTime string. Store the values as "ticks" (seconds or milliseconds since epoch, also known as Unix Time).

Current time in ticks:

   // C#
   DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
   // JavaScript
   new Date().value

If you want to go back 24hrs, just substract the equivalent ticks from the time. 24 * 60 * 60 * 1000

Second of all: At the very least get rid of the timezones. Your implementation seems to be highly reliable on having only one timezone. Ever. Your writing clients and the CosmosDB server would need to run in the same timezone, guaranteed. Please use DateTime.UtcNow instead of DateTime.Now. Or DateTimeOffset.

(If you are not manually calling ToString before writing your data to CosmosDB, it will already have been stipped of the timezone, see Creating and comparing dates inside CosmosDB stored procedures)

 // C#
 DateTimeOffset.UtcNow.ToString("s");
 // JavaScript
 new Date().toISOString();

This is a sample stored procedure:

https://github.com/Azure/azure-documentdb-js-server/blob/master/samples/stored-procedures/SimpleScript.js

function simple(prefix) {
    var collection = getContext().getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
        function (err, feed, options) {
          ...
        });
}

It feels like you are trying to ask how to translate your code into JavaScript.

    SqlQuerySpec query = new SqlQuerySpec("SELECT * FROM c where 
         (c.logdatetime between '" + DateTime.Now.AddDays(-1)
          .ToString("yyyy-MM-ddTHH:mm:ssZ") + "' and '" + DateTime.Now
          .ToString("yyyy-MM-ddTHH:mm:ssZ") + "')");

Which would be something like this using ticks:

var now = new Date().value;
var yesterday = now - 24 * 60 * 60 * 1000;

var query = "SELECT * FROM c where (c.logdatetime between '" + yesterday + "' and '" + now + "')"

If you need to keep the strings:

var now = new Date();
var yesterday = new Date(now.value - 24 * 60 * 60 * 1000);

var query = "SELECT * FROM c where (c.logdatetime between '" + yesterday.toISOString() + "' and '" + now.toISOString() + "')"

Not sure how perfect this behaves when daylight savings time comes into play, but you should find plenty of resources for that if that is critical for you.

Azure Document DB Stored procedure DateTime query

1 Answers1