New date and time system functions in Azure Cosmos DB

Tim Sander

We are excited to announce support for the following system functions that help developers convert date and time values:

• DateTimeAdd
• DateTimeDiff
• DateTimeFromParts
• DateTimePart
• DateTimeToTicks
• DateTimeToTimestamp
• TicksToDateTime
• TimestampToDateTime

These new system functions are available in all current SQL (core) API SDK’s. For the .NET SDK, these system functions are available in version 3.13 or later. Learn about existing date and time system functions.

Let’s check out some examples for working with dates and times in Azure Cosmos DB, including some examples that use these new system functions. As a reminder, Azure Cosmos DB stores DateTime values as regular strings. There is no special storage format for DateTime values.

The recommended format for DateTime strings in Azure Cosmos DB is yyyy-MM-ddTHH:mm:ss.fffffffZ which follows the ISO 8601 UTC standard. You could, alternatively, use an epoch value to represent time as a number, in either milliseconds or 100-nanosecond ticks.  Comparing different date and time values, requires a consistent format. For example, you cannot compare an ISO 8601 UTC standard DateTime string to an epoch value without the appropriate DateTimeToTimestamp or DateTimeToTicks conversion.

Comparing DateTime strings

Both equality and range comparisons between DateTime values in the format yyyy-MM-ddTHH:mm:ss.fffffffZ  are supported and will utilize the index.

Query:

SELECT ("2020-05-05T19:21:27.0000000Z" > "2020-09-05T00:00:0.1234567Z") AS ComparisonResult

Result:

[
    {
        "ComparisonResult": false
    }
]

Since DateTime strings are treated as ordinary strings by the query engine, DateTime strings must have a consistent precision for valid equality comparisons.

Identifying the current month based on a DateTime strings

You can use the new DateTimePart system function to calculate the current month (or other unit of time) based on a DateTime string.

Query:

SELECT DateTimePart("m", "2020-01-02T03:04:05.6789123Z") AS MonthValue

Result:

[
    {
        "MonthValue": 1
    }
]

Constructing a DateTime from parts

If you have the individual components, you can now construct a DateTime string on the fly.

Query:

SELECT DateTimeFromParts(2020, 9, 4, 10, 52, 12, 3456789) AS DateTime

Result:

[
    {
        "DateTime": "2020-09-04T10:52:12.3456789Z"
    }
]

Converting the system _ts property to a DateTime string

Items in Cosmos DB have a _ts system property. The _ts value is an epoch value in seconds (not milliseconds) since an item was last modified. To convert the _ts value from seconds to milliseconds or 100-nanosecond ticks, simply multiply by 1,000 or 10,000,000, respectively. You can then convert this value to a DateTime.

Query:

SELECT TOP 1 TimestampToDateTime(c._ts*1000) AS DateTime
FROM c

Result:
[
    {
        "DateTime": "2019-08-30T18:49:11.0000000Z"
    }
]

Converting to local time zone

If you need to convert a DateTime value from UTC time to a local time zone, you can do the conversion using the DateTimeAdd system function.

Here’s an example that converts a UTC time to India Standard Time (UTC+5:30)

Query:

SELECT DateTimeAdd("minute", 330, "2020-01-01T00:00:00.1234567Z") AS IndiaStandardTime

Result:

[
    {
        "IndiaStandardTime": "2020-01-01T05:30:00.1234567Z"
    }
]

Learn more:

• Working with dates in Azure Cosmos DB
• Date and time system functions

Tim Sander Program Manager, Azure Cosmos DB

Follow