Convert Timescript to Date in Azure Cosmosdb SQL Query

How to query from cosmosdb in azure function with typescript

You can use @azure/cosmos package with javascript to read the data from the collection.

const CosmosClient = require("@azure/cosmos").CosmosClient;

getUser: async function (login) {
    const querySpec = {
      query: 'SELECT * FROM users',
      parameters: [
      ]
    };

    const result = await cosmosClient
      .database(databaseId)
      .container(containerId)
      .items.query(querySpec)
      .fetchNext();

    if (result && result.resources) {
      return result.resources[0];
    }

    return undefined;
  }

Here is a sample

Customize SQL query input binding in Azure Function (TypeScript)

I discovered that the normal JavaScript package for Costmos DB access works within Azure Functions as well. So instead of using an input binding, I just needed to import the @azure/cosmos package and use it directly.

This was my reference: https://learn.microsoft.com/en-us/azure/developer/javascript/how-to/with-database/use-sql-api-as-cosmos-db.

Steps:

1. npm install @azure/cosmos
2. npm install --save-dev @types/node (I don't know why this was needed, but I got errors otherwise. Maybe the Azure Functions runtime for TypeScript is a modified version of node?)
3. Use the CosmosClient class as described in the link above

Note: Because you're creating the connection yourself, make sure to follow the guidelines here to minimize processing time and connection exhaustion: https://learn.microsoft.com/en-us/azure/azure-functions/manage-connections#azure-cosmos-db-clients

Convert SQL date number into date using javascript

What you have is a day count from 1900-01-01 and you need to add the given days.

function convert2date(days) {    const        isLeapYear = year => (!(year % 4) && !!(year % 100)) || !(year % 400),        daysInMonth = (month, year) => [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1] + isLeapYear(year);

    var date = [1900, 1, 1],        y = isLeapYear(date[0]) + 365,        d;        while (days >= y) {        date[0]++;        days -= y;        y = isLeapYear(date[0]) + 365;    }        d = daysInMonth(date[1], date[0]);    while (days >= d) {        date[1]++;        days -= d;        d = daysInMonth(date[1], date[0]);    }    date[2] += days;        return date.map((v, i) => v.toString().padStart(i ? 2 : 4, 0)).join('-');}
console.log(convert2date(40702)); // 2011-06-10console.log(convert2date(43811)); // 2019-12-14

Creating and comparing dates inside CosmosDB stored procedures

As far as I can see, CosmosDB is storing DateTime values without the corresponding Timezone, aka. not as DateTimeOffset. This means it should not matter where the code is executed, since it is always normalized to something like this:

  "2014-09-15T23:14:25.7251173Z"

Javascript Date object are timestamps - they merely contain a number of milliseconds since the epoch. There is no timezone info in a Date object. Which calendar date (day, minutes, seconds) this timestamp represents is a matter of the interpretation (one of to...String methods).

(taken from Parse date without timezone javascript)

In other words, no matter where you are in the world, new Date() will always have the same value internally.

If you want to remove uncertainty in exchange for readability, I would recommend only storing the seconds or milliseconds since the epoch (Unix Time). This is also what is used internally by date (new Date().value - milliseconds). Incidentally, the internal cosmos document field _ts is also a timestamp in epoch format.

Be aware that the value of new Date() might by off the 'correct global time` by a couple of minutes - I don't know if Azure/Cosmos guarantees a certain deviation window.

---

Related Topics