时间戳聚合查询某一天的数据
{
"_id": {
"$oid": "5ff83089c3db080017fdc968"},
"app_id": 96,
"timestamp": 1610100873416
},
{
"_id": {
"$oid": "5ff8303ac3db080017fdc966"},
"app_id": 96,
"timestamp": 1610100794510
}
聚合查询SQL:
比如我想查询的SQL是:
select day ,count(*) from xxx where app_id =xxx group by app_id , day
在mongo中将时间戳转化为时间的SQL如下:
mongo将时间戳转为日期格式
db.xxx.aggregate(
[
{
$project: {
timestamp: 1,
app_id: 1,
day: {
$dateToString: {
format: "%Y-%m-%d %H:%M:%S:%L", date:{
"$add":[new Date(0),"$timestamp"]}}},
day8: {
$dateToString: {
format: "%Y-%m-%d %H:%M:%S:%L", date:{
"$add":[new Date(0),"$timestamp",28800000]}}}
}
}
]
)
转换的结果如下:day8是多个8个小时的结果
{
"_id": {
"$oid": "5fb12ea4c33c7400175b9cb5"},
"app_id": 45,
"day": "2020-10-23 10:02:12:461",
"day8": "2020-10-23 18:02:12:461",
"timestamp": 1603447332461
},
{
"_id": {
"$oid": "5fb12ea4c33c7400175b9cb6"},
"app_id": 45,
"day": "2020-10-23 10:02:12:503",
"day8": "2020-10-23 18:02:12:503",
"timestamp": 1603447332503
}
match和project组合查询
db.xxxx.aggregate(
{
$match:
{
"app_id":96,
"timestamp": {
"$exists": true},
"timestamp": {
"$gte": 1610035200000,"$lte" :1610121600000}
}
},
{
$project: {
timestamp: 1,
app_id: 1,
day: {
$dateToString: {
format: "%Y-%m-%d %H:%M:%S:%L", date:{
"$add":[new Date(0),"$timestamp"