在聚合期间从 MongoDB ISODate 以秒为单位获取 unix 时间戳 [英] Getting unix timestamp in seconds out of MongoDB ISODate during aggregation

问题描述

我正在寻找这个，但我找不到任何有用的东西来解决我的案子.我想要的是在聚合期间从 MongoDB ISODate 中以秒为单位获取 unix 时间戳.问题是我可以从 ISODate 中获取时间戳，但它以毫秒为单位.所以我需要减少那些毫秒.我试过的是:

I was searching for this one but I couldn't find anything useful to solve my case. What I want is to get the unix timestamp in seconds out of MongoDB ISODate during aggregation. The problem is that I can get the timestamp out of ISODate but it's in milliseconds. So I would need to cut out those milliseconds. What I've tried is:

> db.data.aggregate([
    {$match: {dt:2}}, 
    {$project: {timestamp: {$concat: [{$substr: ["$md", 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
  ])

如您所见，我正在尝试从 'md' var 中获取时间戳，并将该时间戳与 '01' 和 'id' 数字连接起来.上面的代码给出:

As you can see I'm trying to get the timestamp out of 'md' var and also concatenate this timestamp with '01' and the 'id' number. The above code gives:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "2014-02-10T16:20:56011141"
}

然后我改进了命令:

> db.data.aggregate([
    {$match: {dt:2}},
    {$project: {timestamp: {$concat: [{$substr: [{$subtract: ["$md", new Date('1970-01-01')]}, 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
  ])

现在我明白了:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "1392049256000011141"
}

我真正需要的是 1392049256011141，所以没有 3 个额外的 000.我尝试使用 $subtract:

What I really need is 1392049256011141 so without the 3 extra 000. I tried with $subtract:

> db.data.aggregate([
    {$match: {dt:2}}, 
    {$project: {timestamp: {$concat: [{$substr: [{$divide: [{$subtract: ["$md", new Date('1970-01-01')]}, 1000]}, 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
  ])

我得到的是:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "1.39205e+009011141"
}

不完全符合我对命令的期望.不幸的是， $substr 运算符不允许负长度.有人有其他解决方案吗?

Not exactly what I would expect from the command. Unfortunately the $substr operator doesn't allow negative length. Does anyone have any other solution?

推荐答案

我不确定为什么您认为需要以秒为单位的值而不是毫秒，因为通常这两种形式都是有效的，并且在大多数语言实现中，毫秒实际上是首选.但一般来说，试图将其强制转换为字符串是解决此问题的错误方法，通常您只需进行数学计算即可:

I'm not sure why you think you need the value in seconds rather than milliseconds as generally both forms are valid and within most language implementations the milliseconds is actually preferred. But generally speaking, trying to coerce this into a string is the wrong way to go around this, and generally you just do the math:

db.data.aggregate([
  { "$project": {
    "timestamp": {
      "$subtract": [
        { "$divide": [
            { "$subtract": [ "$md", new Date("1970-01-01") ] },
            1000
        ]},
        { "$mod": [
          { "$divide": [
              { "$subtract": [ "$md", new Date("1970-01-01") ] },
              1000
          ]},
          1
        ]}
      ]
    }
  }}
])

它以秒为单位返回一个纪元时间戳.基本上是从一个 BSON 日期对象减去另一个 BSON 日期对象得出的，结果是以毫秒为单位的时间间隔.使用1970-01-01"的初始纪元日期实质上是从当前日期值中提取毫秒值.$divide 操作符基本上去掉了毫秒部分和 $mod 取模以实现四舍五入.

Which returns you an epoch timestamp in seconds. Basically derived from when one BSON date object is subtracted from another one then the result is the time interval in milliseconds. Using the initial epoch date of "1970-01-01" results in essentially extracting the milliseconds value from the current date value. The $divide operator essentially takes off the milliseconds portion and the $mod does the modulo to implement rounding.

实际上，尽管您最好使用本机语言为您的应用程序进行工作，因为所有 BSON 日期都将作为本机日期/日期时间"类型在那里返回，您可以在其中提取时间戳值.考虑一下 shell 中的 JavaScript 基础知识:

Really though you are better off doing the work in the native language for your application as all BSON dates will be returned there as a native "date/datetime" type where you can extract the timestamp value. Consider the JavaScript basics in the shell:

var date = new Date()
( date.valueOf() / 1000 ) - ( ( date.valueOf() / 1000 ) % 1 )

通常，对于聚合，您希望对时间戳值进行这种数学运算"，以用于诸如在一天等时间段内聚合值之类的事情.聚合框架可以使用日期运算符，但您也可以使用日期数学方法进行操作:

Typically with aggregation you want to do this sort of "math" to a timestamp value for use in something like aggregating values within a time period such as a day. There are date operators available to the aggregation framework, but you can also do it the date math way:

db.data.aggregate([
    { "$group": {
        "_id": {
          "$subtract": [
              { "$subtract": [ "$md", new Date("1970-01-01") ] },
              { "$mod": [
                  { "$subtract": [ "$md", new Date("1970-01-01") ] },
                  1000 * 60 * 60 * 24
              ]}
          ]
        },
        "count": { "$sum": 1 }
    }}
])

这种形式更典型的是发出四舍五入到一天的时间戳，并在这些时间间隔内聚合结果.

That form would be more typical to emit a timestamp rounded to a day, and aggregate the results within those intervals.

因此，您将聚合框架用于提取时间戳的目的似乎不是最佳用法，或者实际上没有必要将其转换为秒而不是毫秒.在您的应用程序代码中，我认为您应该这样做，除非您确实想要在可以应用日期数学的时间间隔内的结果，如图所示.

So your purposing of the aggregation framework just to extract a timestamp does not seem to be the best usage or indeed it should not be necessary to convert this to seconds rather than milliseconds. In your application code is where I think you should be doing that unless of course you actually want results for intervals of time where you can apply the date math as shown.

方法就在那里，但除非您真正进行聚合，否则这将是您的应用程序性能最差的选择.改为在代码中进行转换.

The methods are there, but unless you are actually aggregating then this would be the worst performance option for your application. Do the conversion in code instead.

这篇关于在聚合期间从 MongoDB ISODate 以秒为单位获取 unix 时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！