在MongoDB中实现类似MySQL时间DATE_FORMAT分组

我们在使用MySQL的时候如果数据库存的是时间戳但又想按照天等时间方式分组，这时候可以使用DATE_FORMAT函数进行格式化时间字符串。那么在MongoDB中有类似的需求应该如何实现呢？

答案就是：aggregate聚合官方文档在此aggregation

我们把SQL 与 Aggergation 对比下：

SQL Terms, Functions, and Concepts	MongoDB Aggregation Operators
WHERE	$match
HAVING	$match
SELECT	$project
ORDER BY	$sort
LIMIT	$limit
SUM()	$sum
COUNT()	$sum
COUNT()	$sortByCount
join	$lookup

mongodb的聚合框架（aggregate）提供了很多修改器用来修改去获取IOSDate类型的字段的年、月、日、时、分、秒、等；同时mongodb也提供了相关的修改器去把IOSDate类型的时间转换为通常我们可以接受的时间格式；

$dayOfYear: 返回该日期是这一年的第几天。（全年366天）
$dayOfMonth: 返回该日期是这一个月的第几天。（1到31）
$dayOfWeek: 返回的是这个周的星期几。（1：星期日，7：星期六）
$year: 返回该日期的年份部分
$month：返回该日期的月份部分（between 1and12.）
$week：返回该日期是所在年的第几个星期（between 0and53）
$hour：返回该日期的小时部分
$minute: 返回该日期的分钟部分
$second: 返回该日期的秒部分（以0到59之间的数字形式返回日期的第二部分，但可以是60来计算闰秒。）
$millisecond：返回该日期的毫秒部分（between 0and999.）
$dateToString：{ $dateToString: { format: <formatString>, date: <dateExpression> } }

上面的date的值需要是mongo中的Date类型

formatString：需要返回的日期式，日期格式通常为以：

%Y	Year (4 digits, zero padded)	0000-9999
%m	Month (2 digits, zero padded)	01-12
%d	Day of Month (2 digits, zero padded)	01-31
%H	Hour (2 digits, zero padded, 24-hour clock)	00-23
%M	Minute (2 digits, zero padded)	00-59
%S	Second (2 digits, zero padded)	00-60
%L	Millisecond (3 digits, zero padded)	000-999
%j	Day of year (3 digits, zero padded)	001-366
%w	Day of week (1-Sunday, 7-Saturday)	1-7
%U	Week of year (2 digits, zero padded)	00-53
%%	Percent Character as a Literal

我们实现准备一些数据看下数据结构

db.sms_log.find().limit(1);

[
    {
        _id: ObjectId("5ed3d4f781dbb4a0205baab3"),
        phone: "13657873286",
        client: 3,
        create_time: 1590940919
    }
]

我们将其中的时间格式化输出试下

db.sms_log.aggregate(
   [
     {
       $project: {
          create_time: 1,
          date1Str: {$dateToString: {format: "%Y-%m-%d %H:%M:%S:%L", date:{"$add":[new Date(0),"$create_time"]}}},
          date2Str: {$dateToString: {format: "%Y-%m-%d %H:%M:%S:%L", date:{"$add":[new Date(0),"$create_time",28800000]}}}
       }
     }
   ]
)

# 输出
[
    {
        _id: ObjectId("5ed3d4f781dbb4a0205baab3"),
        create_time: 1590940919,
        date1Str: "1970-01-19 09:55:40:919",
        date2Str: "1970-01-19 17:55:40:919"
    }
]

“$add”:[new Date(0),”$timestamp”] ，这是为了把$timestamp的值转为Date类型

我们发现时间并没有正常转换成对应的时间，并且date1Str时间比date2Str晚了八个小时(date2Str是正确的。)

通过将值乘以1000将createTs字段转换为毫秒时间戳，再将其转为Date类型即可转为正确的时间。

db.sms_log.aggregate(
   [
     {
       $project: {
          create_time: 1,
          date1Str: {$dateToString: {format: "%Y-%m-%d %H:%M:%S:%L", date:{"$add":[new Date(0),{"$multiply":["$create_time",1000]}]}}},
          date2Str: {$dateToString: {format: "%Y-%m-%d %H:%M:%S:%L", date:{"$add":[new Date(0),{"$multiply":["$create_time",1000]},28800000]}}}
       }
     }
   ]
)

# 结果
[
    {
        _id: ObjectId("5ed3d4f781dbb4a0205baab3"),
        create_time: 1590940919,
        date1Str: "2020-05-31 16:01:59:000",
        date2Str: "2020-06-01 00:01:59:000"
    }
]

$multiply 将数字相乘以返回产品。接受任意数量的参数表达式。

将毫秒时间添加到零毫秒Date(0)对象然后格式化时间

我们知道了如何正确格式化时间现在按照时间天分组获取client等3的每天的量

db.sms_log.aggregate(
  [
    { 
      $match: 
      {
        "sms_client":5,
        "send_status": 1,
      }
    }, 
    {"$group": 
     { 
       "_id":  {$dateToString: {format: "%Y-%m-%d", date:{"$add":[new Date(0),{"$multiply":["$create_time",1000]},28800000]}}}, "count" : { "$sum" : 1 }}}
  ]
);

# 结果
[
    {
        _id: "2020-07-21",
        count: 5583
    },
]

这样我们就实现和MySQL类似的按照时间分组的功能了。

参考阅读：

mongodb aggregate 基于UNIX时间戳的聚合

Mongodb聚合框架Aggregate - 时间戳转时间聚合分组统计实现