Azure Cosmos DB for NoSQL: Date and Time Functions with Examples

In the modern world of data management, date and time operations play a crucial role in various applications and analytical tasks. Azure Cosmos DB, Microsoft’s globally distributed, multi-model NoSQL database service, offers a range of date and time functions to efficiently handle temporal data. In this blog post, we will focus on three essential date and time functions provided by Azure Cosmos DB’s SQL API: GetCurrentDateTime, GetCurrentTicks, and GetCurrentTimestamp. We will explore the usage of each function through illustrative examples.

Understanding Date and Time Functions in Azure Cosmos DB

Before delving into the examples, let’s briefly understand each of the functions:

1. GetCurrentDateTime: The GetCurrentDateTime function returns the current date and time in Coordinated Universal Time (UTC) as a string in ISO 8601 format (e.g., “2023-07-24T12:34:56.789Z”).
2. GetCurrentTicks: The GetCurrentTicks function returns the current date and time as the number of ticks since the Unix epoch (January 1, 1970, 00:00:00 UTC).
3. GetCurrentTimestamp: The GetCurrentTimestamp function returns the current timestamp in seconds since the Unix epoch (January 1, 1970, 00:00:00 UTC) as a numeric value.

Sample Data

To demonstrate the GetCurrentDateTime, GetCurrentTicks, and GetCurrentTimestamp functions, let’s consider a container called “events” in our Azure Cosmos DB database. We will create documents representing various events, and each document will look like this:

GetCurrentDateTime Function Example

Suppose we want to add a new event to our “events” container and capture the current date and time when the event was added. We can use the GetCurrentDateTime function as follows:

In this query, GetCurrentDateTime() generates the current date and time in UTC, which is then inserted as the eventDate property for the new event. The INSERT INTO statement adds the new event document to the “events” container.

GetCurrentTicks Function Example

Let’s explore an example where we want to store the current date and time as the number of ticks since the Unix epoch when recording an event’s occurrence. We can use the GetCurrentTicks function as follows:

In this query, GetCurrentTicks() retrieves the current date and time and converts it into the number of ticks since the Unix epoch. The INSERT INTO statement adds the new event document to the “events” container, including the eventTimestamp property with the number of ticks.

GetCurrentTimestamp Function Example

Suppose we need to record the current timestamp (in seconds since the Unix epoch) when a user performs a specific action. We can use the GetCurrentTimestamp function as follows:

In this query, GetCurrentTimestamp() obtains the current timestamp in seconds since the Unix epoch. The UPDATE statement modifies the “events” container by adding the userActionTimestamp property with the current timestamp for the event with eventId equal to “12345.”

Conclusion

In this blog post, we explored three essential date and time functions provided by Azure Cosmos DB’s SQL API: GetCurrentDateTime, GetCurrentTicks, and GetCurrentTimestamp. These functions empower developers and businesses to efficiently manage temporal data, capture current date and time values, and record event occurrences accurately.

Whether it’s capturing timestamps, calculating ticks, or recording the current date and time, Azure Cosmos DB’s date and time functions provide the flexibility and precision required for various time-based operations.

By leveraging the capabilities of Azure Cosmos DB and its date and time functions, businesses can build data-driven applications that effectively manage temporal data, ensure data accuracy, and support real-time analytics.

So, embrace the potential of Azure Cosmos DB’s date and time functions and unlock the true value of temporal data to drive your business forward.

Happy timekeeping!