We are excited to announce support for the following system functions that help developers convert date and time values:<\/p>\n
These new system functions are available in all current SQL (core) API SDK\u2019s. For the .NET SDK, these system functions are available in version 3.13<\/a> or later. Learn about existing date and time system functions<\/a>.<\/p>\n Let\u2019s 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.<\/p>\n The recommended format for DateTime strings in Azure Cosmos DB is\u00a0yyyy-MM-ddTHH:mm:ss.fffffffZ<\/strong>\u00a0which follows the ISO 8601 UTC standard<\/a>. You could, alternatively, use an epoch value to represent time as a number, in either milliseconds<\/a> or 100-nanosecond ticks<\/a>. \u00a0Comparing 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<\/a> or DateTimeToTicks<\/a> conversion.<\/p>\n <\/p>\n Both equality and range comparisons between DateTime values in the format yyyy-MM-ddTHH:mm:ss.fffffffZ<\/strong>\u00a0 are supported and will utilize the index.<\/p>\n Query:<\/strong><\/p>\n Result:<\/strong><\/p>\n Since DateTime strings are treated as ordinary strings by the query engine, DateTime strings must have a consistent precision for valid equality comparisons.<\/p>\n <\/p>\n You can use the new DateTimePart<\/a> system function to calculate the current month (or other unit of time) based on a DateTime string.<\/p>\n Query:<\/strong><\/p>\n Result:<\/strong><\/p>\n If you have the individual components, you can now construct a DateTime string on the fly.<\/p>\n Query:<\/strong><\/p>\n Result:<\/strong><\/p>\n \u00a0<\/strong><\/p>\n 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.<\/p>\n Query:<\/strong><\/p>\n If you need to convert a DateTime value from UTC time to a local time zone, you can do the conversion using the DateTimeAdd<\/a> system function.<\/p>\n Here\u2019s an example that converts a UTC time to India Standard Time (UTC+5:30)<\/p>\n Query:<\/strong><\/p>\n Result:<\/strong><\/p>\n Learn more:<\/strong><\/p>\n Learn about new system functions for working with dates and times in Azure Cosmos DB<\/p>\n","protected":false},"author":12128,"featured_media":61,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[12,14,818,19],"tags":[1752],"class_list":["post-1768","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-announcements","category-core-sql-api","category-query","category-tips-and-tricks","tag-datetime"],"acf":[],"blog_post_summary":" Learn about new system functions for working with dates and times in Azure Cosmos DB<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/1768","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/12128"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=1768"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/1768\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/61"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=1768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=1768"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=1768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Comparing DateTime strings<\/span><\/h2>\n
SELECT (\"2020-05-05T19:21:27.0000000Z\" > \"2020-09-05T00:00:0.1234567Z\") AS ComparisonResult<\/pre>\n
[\r\n \u00a0\u00a0 {\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"ComparisonResult\": false\r\n \u00a0\u00a0 }\r\n]<\/pre>\nIdentifying the current month based on a DateTime strings<\/span><\/h2>\n
SELECT DateTimePart(\"m\", \"2020-01-02T03:04:05.6789123Z\") AS MonthValue<\/pre>\n
[\r\n \u00a0\u00a0 {\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"MonthValue\": 1\r\n \u00a0\u00a0 }\r\n]<\/pre>\nConstructing a DateTime from parts<\/span><\/h2>\n
SELECT DateTimeFromParts(2020, 9, 4, 10, 52, 12, 3456789) AS DateTime<\/pre>\n
[\r\n \u00a0\u00a0 {\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"DateTime\": \"2020-09-04T10:52:12.3456789Z\"\r\n \u00a0\u00a0 }\r\n]<\/pre>\nConverting the system _ts property to a DateTime string<\/span><\/h2>\n
SELECT TOP 1 TimestampToDateTime(c._ts*1000) AS DateTime\r\nFROM c<\/pre>\n
Result:<\/strong>\r\n[\r\n {\r\n \"DateTime\": \"2019-08-30T18:49:11.0000000Z\"\r\n }\r\n]<\/pre>\nConverting to local time zone<\/span><\/h2>\n
SELECT DateTimeAdd(\"minute\", 330, \"2020-01-01T00:00:00.1234567Z\") AS IndiaStandardTime<\/pre>\n
[\r\n \u00a0\u00a0 {\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"IndiaStandardTime\": \"2020-01-01T05:30:00.1234567Z\"\r\n \u00a0\u00a0 }\r\n]\r\n\r\n<\/pre>\n\n