New date and time system functions in Azure Cosmos DB

Tim

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

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:

2 comments

Comments are closed. Login to edit/delete your existing comments

  • Gunnar Dalsnes

    Documentation is a bug unclear:
    DateTimeToTicks
    DateTime
    UTC date and time ISO 8601 string value in the format YYYY-MM-DDThh:mm:ss.fffffffZ
    I tested it and it will parse shorter formats too, but seems to always require T and Z, except for this one strange case:
    Will parse: “2020-01-01”, will not parse: “2020-01-01Z” (maybe weird, I would have done it the opposite or maybe not allowed to parse local time “2020-01-01”)
    Edit2: DateTime and DateTimeOffset will parse “2020-01-01Z” correctly, although it’s possibly not really a part of ISO 8601…
    Edit3: SELECT DateTimeToTicks(“2020-01-01T00:00:00Z”) and SELECT DateTimeToTicks(“2020-01-01”) produce the same (15778368000000000). I would almost call this a bug:-)

    But good work and very nice to have. Also nice to see you make the format YYYY-MM-DDThh:mm:ss.fffffffZ more of a requirement than a suggestion (that’s how I feel about it at least).

    Edit: also strange, I can not go past 1601:
    SELECT DateTimeToTicks(“1601-01-01T00:00:00.0000000Z”) -> -116444736000000000
    SELECT DateTimeToTicks(“1600-01-01T00:00:00.0000000Z”) -> err
    What is so special about 1601:-D (The Windows NT time epoch)