聚合期间从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"
}

然后我使用以下命令改进了命令:

Then I improved the command with:

> 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日期都将作为本机"date/datetime"类型返回到那里，您可以在其中提取时间戳记值.考虑外壳中的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屋！