使用 Azure Cosmos DB 中的日期Working with Dates in Azure Cosmos DB

Azure Cosmos DB 通过本机 JSON 数据模型提供架构灵活性和丰富的索引。Azure Cosmos DB delivers schema flexibility and rich indexing via a native JSON data model. 所有 Azure Cosmos DB 资源(包括数据库、容器、文档和存储过程)均作为 JSON 文档进行建模和存储。All Azure Cosmos DB resources including databases, containers, documents, and stored procedures are modeled and stored as JSON documents. 为使代码可移植,JSON(及 Azure Cosmos DB)仅支持一小组基本类型:字符串、数字、布尔值、数组、对象和 Null。As a requirement for being portable, JSON (and Azure Cosmos DB) supports only a small set of basic types: String, Number, Boolean, Array, Object, and Null. 但是,JSON 具有相当的灵活性,允许开发人员和框架使用这些基元并将其编写为对象或数组,以便表示更复杂的类型。However, JSON is flexible and allow developers and frameworks to represent more complex types using these primitives and composing them as objects or arrays.

除了基本类型,许多应用程序还需要表示日期和时间戳的 DateTime 类型。In addition to the basic types, many applications need the DateTime type to represent dates and timestamps. 本文介绍开发人员可如何使用 .NET SDK 在 Azure Cosmos DB 中存储、检索和查询日期。This article describes how developers can store, retrieve, and query dates in Azure Cosmos DB using the .NET SDK.

存储 DateTimeStoring DateTimes

Azure Cosmos DB 支持 JSON 类型,如字符串、数字、布尔值、null、数组和对象。Azure Cosmos DB supports JSON types such as - string, number, boolean, null, array, object. 它不直接支持 DateTime 类型。It does not directly support a DateTime type. 目前,Azure Cosmos DB 不支持日期的本地化。Currently, Azure Cosmos DB doesn't support localization of dates. 因此,需要将 DateTime 存储为字符串。So, you need to store DateTimes as strings. Azure Cosmos DB 中 DateTime 字符串的建议格式为 YYYY-MM-DDThh:mm:ss.sssZ,它遵循 ISO 8601 UTC 标准。The recommended format for DateTime strings in Azure Cosmos DB is YYYY-MM-DDThh:mm:ss.sssZ which follows the ISO 8601 UTC standard. 建议以 UTC 格式存储 Azure Cosmos DB 中的所有日期。It is recommended to store all dates in Azure Cosmos DB as UTC. 将日期字符串转换为此格式将允许按字典顺序对日期进行排序。Converting the date strings to this format will allow sorting dates lexicographically. 如果存储非 UTC 日期,则必须在客户端处理相关逻辑。If non-UTC dates are stored, the logic must be handled at the client-side. 若要将本地 DateTime 转换为 UTC,偏移量必须已知/存储为 JSON 中的属性,并且客户端可以使用偏移量来计算 UTC DateTime 值。To convert a local DateTime to UTC, the offset must be known/stored as a property in the JSON and the client can use the offset to compute UTC DateTime value.

由于以下原因,大多数应用程序可以使用 DateTime 的默认字符串表示形式:Most applications can use the default string representation for DateTime for the following reasons:

  • 字符串可以进行比较,而 DateTime 值的相对顺序在这些值转换为字符串时得以保留。Strings can be compared, and the relative ordering of the DateTime values is preserved when they are transformed to strings.
  • 此方法不需要进行 JSON 转换所需的任何自定义代码或属性。This approach doesn't require any custom code or attributes for JSON conversion.
  • 在 JSON 中存储的日期人工可读。The dates as stored in JSON are human readable.
  • 这种方法可以利用 Azure Cosmos DB 的索引执行快速查询。This approach can take advantage of Azure Cosmos DB's index for fast query performance.

例如,以下代码片段使用 .NET SDK 将 Order 对象存储为文档,该对象包含两个 DateTime 属性 - ShipDateOrderDateFor example, the following snippet stores an Order object containing two DateTime properties - ShipDate and OrderDate as a document using the .NET SDK:

public class Order
{
    [JsonProperty(PropertyName="id")]
    public string Id { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime ShipDate { get; set; }
    public double Total { get; set; }
}

await client.CreateDocumentAsync("/dbs/orderdb/colls/orders", 
    new Order 
    { 
        Id = "09152014101",
        OrderDate = DateTime.UtcNow.AddDays(-30),
        ShipDate = DateTime.UtcNow.AddDays(-14), 
        Total = 113.39
    });

本文档存储在 Azure Cosmos DB 中,如下所示:This document is stored in Azure Cosmos DB as follows:

{
    "id": "09152014101",
    "OrderDate": "2014-09-15T23:14:25.7251173Z",
    "ShipDate": "2014-09-30T23:14:25.7251173Z",
    "Total": 113.39
}

也可将 DateTime 存储为 Unix 时间戳,即存储为数字,用于表示自 1970 年 1 月 1 日以来已过去的秒数。Alternatively, you can store DateTimes as Unix timestamps, that is, as a number representing the number of elapsed seconds since January 1, 1970. Azure Cosmos DB 的内部时间戳 (_ts) 属性遵循这种方法。Azure Cosmos DB's internal Timestamp (_ts) property follows this approach. 可以使用 UnixDateTimeConverter 类将 DateTime 序列化为数字。You can use the UnixDateTimeConverter class to serialize DateTimes as numbers.

编制 DateTime 的索引以执行范围查询Indexing DateTimes for range queries

通常可以使用 DateTime 值执行范围查询。Range queries are common with DateTime values. 例如,如果需要查找昨天以来创建的所有订单,或者查找过去五分钟内发运的所有订单,则要执行范围查询。For example, if you need to find all orders created since yesterday, or find all orders shipped in the last five minutes, you need to perform range queries. 若要有效执行这些查询,必须针对字符串的范围索引配置集合。To execute these queries efficiently, you must configure your collection for Range indexing on strings.

DocumentCollection collection = new DocumentCollection { Id = "orders" };
collection.IndexingPolicy = new IndexingPolicy(new RangeIndex(DataType.String) { Precision = -1 });
await client.CreateDocumentCollectionAsync("/dbs/orderdb", collection);

如需详细了解如何配置索引策略,可参阅 Azure Cosmos DB 索引策略You can learn more about how to configure indexing policies at Azure Cosmos DB Indexing Policies.

在 LINQ 中查询 DatetimeQuerying DateTimes in LINQ

SQL .NET SDK 自动支持通过 LINQ 查询存储在 Azure Cosmos DB 中的数据。The SQL .NET SDK automatically supports querying data stored in Azure Cosmos DB via LINQ. 例如,以下代码片段显示一个 LINQ 查询,该查询筛选在过去三天中发运的订单。For example, the following snippet shows a LINQ query that filters orders that were shipped in the last three days.

IQueryable<Order> orders = client.CreateDocumentQuery<Order>("/dbs/orderdb/colls/orders")
    .Where(o => o.ShipDate >= DateTime.UtcNow.AddDays(-3));

// Translated to the following SQL statement and executed on Azure Cosmos DB
SELECT * FROM root WHERE (root["ShipDate"] >= "2016-12-18T21:55:03.45569Z")

可在查询 Cosmos DB 中详细了解 Azure Cosmos DB 的 SQL 查询语言和 LINQ 提供程序。You can learn more about Azure Cosmos DB's SQL query language and the LINQ provider at Querying Cosmos DB.

本文探讨了如何在 Azure Cosmos DB 中存储、索引和查询 DateTime。In this article, we looked at how to store, index, and query DateTimes in Azure Cosmos DB.

后续步骤Next Steps