在聚合期间从 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屋！