mongodb 使用mongod在utc中存储日期时如何处理时区问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18287493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to deal with the timezone issue when storing dates in utc using mongod?
提问by Hrishi
I have a mongodb collection where each document has some attributes and a utc timestamp. I need to pull out data from the collection and use the aggregation framework because I use the data from the collection to display some charts on the user interface. However, I need to do the aggregation as per the user's timezone. Assuming I know the user's timezone(passed in the request from browser or in some other manner), is there any way to use the aggregation framework to aggregate based on the [client's] timezone?
我有一个 mongodb 集合，其中每个文档都有一些属性和一个 utc 时间戳。我需要从集合中提取数据并使用聚合框架，因为我使用集合中的数据在用户界面上显示一些图表。但是，我需要根据用户的时区进行聚合。假设我知道用户的时区（通过浏览器的请求或以其他方式传递），有什么方法可以使用聚合框架根据 [client's] 时区进行聚合？
采纳答案by Matt Johnson-Pint
What you're asking for is currently being discussed in MongoDB issue SERVER-6310.
您所要求的内容目前正在MongoDB 问题 SERVER-6310 中讨论。
I found this in a link from a discussion thread.
我在一个讨论线程的链接中找到了这个。
The problem is common for any grouping by date, including SQL databases and NoSQL databases. In fact, I recently addressed this head on in RavenDB. There is a good description of the problem and a RavenDB solution here.
任何按日期分组的问题都很常见，包括 SQL 数据库和 NoSQL 数据库。事实上，我最近在 RavenDB 中解决了这个问题。有问题，一个解决方案RavenDB一个很好的说明这里。
The MongoDB issues discusses a workaround, which is similar to what I described in comments above. You precalculate the local times you are interested in, and group by those instead.
MongoDB 问题讨论了一种解决方法，这类似于我在上面的评论中描述的内容。您预先计算了您感兴趣的当地时间，然后按这些时间分组。
It will be difficult to cover every time zone in the world with either approach. You should decide on a small handful of target zones that make sense for your user base, such as the per-office approach I described in the RavenDB article.
使用任何一种方法都很难覆盖世界上的每个时区。您应该决定一小部分对您的用户群有意义的目标区域，例如我在 RavenDB 文章中描述的每个办公室的方法。
UPDATE: This issue was solved in MongoDB in July 2017 (version 3.5.11).The solution is described in the first link above, but in short they have introduced a new object format for dates in aggregation expressions: { date: <dateExpression>, timezone: <tzExpression> }that allows you to specify a timezone to use when aggregating. See herefor another example in the Mongo docs.
更新：此问题已于 2017 年 7 月在 MongoDB 中解决（版本 3.5.11）。上面的第一个链接中描述了解决方案，但简而言之，他们为聚合表达式中的日期引入了一种新的对象格式：{ date: <dateExpression>, timezone: <tzExpression> }允许您指定聚合时要使用的时区。有关Mongo 文档中的另一个示例，请参见此处。
回答by Astral
Aside from the SERVER-6310 mentioned by Matt Johnson, one other workaround is to use the $projectoperator to add or subtract from the UTC time zone to "shift the time" into the correct local zone. Turns out you can add or subtract time in milliseconds.
除了 Matt Johnson 提到的 SERVER-6310 之外，另一种解决方法是使用$project运算符从 UTC 时区中添加或减去，以将时间“移动”到正确的本地区域。事实证明，您可以以毫秒为单位增加或减少时间。
For example, assuming I have a Date field called orderTime. I'd like to query for EDT. That is -4 hours from UTC. That's 4 * 60 * 60 * 1000 milliseconds.
例如，假设我有一个名为orderTime. 我想查询 EDT。这是从 UTC 开始的 -4 小时。那是 4 * 60 * 60 * 1000 毫秒。
So I would then write the following projection to get day_orderedin local time for all my records:
因此，我将编写以下投影以获取day_ordered我所有记录的当地时间：
db.table.aggregate(
{ $project : { orderTimeLocal : { $subtract : [ "$orderTime", 14400000] } } },
{ $project : { day_ordered : { $dayOfYear : "$orderTimeLocal" } } })
回答by Sebastian
Every approach suggested above works perfectly fine, but since there is a new version of mongodb, from 2.6 you can use $letin the aggregation framework, this will let you create variables on the fly, thus avoiding the need to $projectbefore grouping. Now you could create a variable with $letthat will hold the localized time, and use it in the $groupoperator.
上面建议的每种方法都可以很好地工作，但是由于有新版本的 mongodb，从 2.6 开始，您可以$let在聚合框架中使用，这将使您可以动态创建变量，从而避免$project分组之前的需要。现在您可以创建一个$let保存本地化时间的变量，并在$group运算符中使用它。
Something like:
就像是：
db.test.aggregate([
{$group: {
_id: {
$let: {
vars: {
local_time: { $subtract: ["$date", 10800000]}
},
in: {
$concat: [{$substr: [{$year: "$$local_time"}, 0, 4]},
"-",
{$substr: [{$month: "$$local_time"}, 0, 2]},
"-",
{$substr: [{$dayOfMonth: "$$local_time"}, 0, 2]}]
}
}
},
count: {$sum: 1}
}
}])
Notice that you use $letinside definition of a block/variable, and the value of that block/variable is the returned value of the subexpression "in", where the above defined vars are used.
请注意，您使用$let了块/变量的内部定义，并且该块/变量的值是 subexpression 的返回值"in"，其中使用了上面定义的变量。
回答by Emir Mamashov
I found a solution in the mongoose pluginto normalize stored dates timezone.
我在mongoose 插件中找到了一个解决方案来规范化存储的日期时区。
