Azure CosmosDB System DateTime functions

Azure CosmosDB System DateTime functions

     Json does not have datetime data type, you need to keep the datetime information in string. This can be a problem for database engines specially if user needs to search by date or sort by date. Cosmos DB team introduced bunch of datetime functions to the Azure Cosmos DB database engine this month. You can read my older post about DateTime in CosmosDB if you like to know how Azure CosmosDB saves the datetime in documents. I will cover the new datetime functions in this post. Here is the list of the functions 

DateTimeAdd

DateTimeDiff

DateTimeFromParts

DateTimePart

DateTimeToTicks

DateTimeToTimestamp

TicksToDateTime

TimestampToDateTime

DateTimeAdd(<DateTimePart>, <number>, <DateTime>)

    DateTimeAdd function adds or subtracts given DateTimePart from the given datetime. Let's look at the available DateTimePart options.

DateTimePart	abbreviations
Year	"year", "yyyy", "yy"
Month	"month","mm","m"
Day	"day","dd",d"
Hour	"hour","hh"
Minute	"minute","mi","n"
Second	"second","ss","s"
Milisecond	"milisecond","ms"
Microsecond	"microsecond","mcs"
Nanosecond	"nanosecond","ns"

     Here is two examples for adding and subtracting datetimepart from a date. GetCurrentDateTime() is a Cosmos DB function which returns the current datetime just like GetDate() in SQL Server. 

SELECT DateTimeAdd("mm", -1, GetCurrentDateTime()) as oneMonthago

SELECT DateTimeAdd("dd", 2, GetCurrentDateTime()) as twoDaysLater

[
    {
        "oneMonthago": "2020-09-29T19:33:48.5193825Z"

    }
]

[
    {
        "twoDayslater": "2020-10-31T19:34:11.5452228Z"
    }
] 
  

DateTimeDiff(<DateTimePart>, <StartDate>, <EndDate>)

     This function returns the number of the given DateTimePart between two dates. In the following example, I pass the current date and one month later date to the function. By changing the datetimepart, I can see how many days and hours between two dates.

SELECT DateTimeDiff("dd", GetCurrentDateTime(),

DateTimeAdd("mm", 1, GetCurrentDateTime())) as DaysDiff

SELECT DateTimeDiff("hh", GetCurrentDateTime(),

DateTimeAdd("mm", 1, GetCurrentDateTime())) as HoursDiff

[
    {
        "DaysDiff": 31
    }
]

[
    {
        "HoursDiff": 744
    }
] 
  

DateTimeFromParts(<year>,<month>,<day>,[hour],[min],[sec],[fractions of sec])

      This function returns a valid Datetime value. JSON documents do not have datetime type so return type will be still string. 

SELECT DateTimeFromParts(2020, 11, 1) as November

SELECT DateTimeFromParts(2020, 11, 1, 20) as NovEightPM

[
    {
        "November": "2020-11-01T00:00:00.0000000Z"
    }
]

[
    {
        "NovEightPM": "2020-11-01T20:00:00.0000000Z"
    }

] 
  

DateTimePart (<DateTimePart>, <DateTime>)

This function returns the value of the specified DateTimePart of the given DateTime 

SELECT DateTimePart("mm", GetCurrentDateTime()) as ThisMonth

SELECT DateTimePart("hh", GetCurrentDateTime()) as ThisHour

[
    {
        "ThisMonth": 10
    }
]

[
    {
        "ThisHour": 19
    }

] 
  

DateTimeToTicks (<DateTime>)

     This function returns the specified DateTime to ticks.A single tick represents 100 nanoseconds. It will start the count from Jan 1, 1970. You can use the GetCurrentTicks() to use the current date to get the number of ticks.

SELECT DateTimeToTicks(GetCurrentDateTime()) as ThisMonth

SELECT DateTimeToTicks(DateTimeAdd("mm", 1, GetCurrentDateTime()) as NextMonth

SELECT GetCurrentTicks() as Current

[
    {
        "ThisMonth": 16040015633406700
    }
]

[
    {
        "NextMonth": 16066801609470156
    }

]

[
    {
        "Current": 16040019248312312
    }

]  

DateTimeToTimeStamp (<DateTime>)

     This format is called Unix Timestamp. You can use GetCurrentTimeStamp() function to get the current Unix Timestamp.

SELECT DateTimeToTimeStamp(GetCurrentDateTime()) as ThisMonthInUnixFormat

SELECT DateTimeToTimeStamp(DateTimeAdd("mm", 1, GetCurrentDateTime())) as NextMonthInUnixFormat

SELECT GetCurrentTimeStamp() as CurrentUnixTimeStamp

[
    {
        "ThisMonthInUnixFormat": 1604069362568
    }
]

[
    {
        "NextMonthInUnixFormat": 1606747734364
    }

]

[
    {
        "CurrentUnixTimeStamp": 1604069268290
    }

]  

TimestampToDateTime (<Unix TimeStamp>)

     You can convert Unix Timestamp to DateTime with this function.

SELECT TimestampToDateTime(GetCurrentTimeStamp()) as ThisMonth

SELECT TimeStampToDateTime(p._ts) as dt FROM Posts p

WHERE p.PostId = 9507

[
    {
        "ThisMonth": "2020-10-30T14:52:27.5490000Z"
    }
]

[
    {
        "dt": "2020-10-30T14:22:17.1220000Z"
    }

]

Comparing DateTime strings

     You can compare datetime strings with quality and range comparison operators. Strings must be in ISO 8601 UTC format (yyyy-MM-ddTHH:mm:ss:fffffffZ)

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

SELECT (GetCurrentDateTime() < DateTimeAdd("mm",1, GetCurrentDateTime())) as result

[
    {
        "result": false
    }
]

[
    {
        "result": true
    }

]