使用Google表格"dd-MM-yyyy HH:ss"在MongoDB上查询ISODate.作为输出 [英] Query ISODate on MongoDB with Google Sheets "dd-MM-yyyy HH:ss" as an output

问题描述

我使用称为Redash的工具在JSON上查询( ) MongoDB.在我的收藏中，日期是按照ISO格式制定的，因此当我将查询导入(使用googlesheet的importdata函数)到工作表时，我必须使用工作表中设计的公式将其转换为适当的格式. 我希望直接在查询中集成此操作，以将ISO日期格式直接以适当的"dd-MM-yyyy HH:ss"格式发送到表格.

I use a tool called Redash to query (in JSON) on MongoDB. In my collections dates are formulated in ISO, so when my query is imported (with google sheets' importdata function) to a sheet, I have to convert it to the appropriate format with a formula designed in the sheet. I would love to integrate this operation directly in my query, that the ISO date format is directly sent to Sheets in the appropriate "dd-MM-yyyy HH:ss" format.

有什么想法吗?

非常感谢

推荐答案

您也许可以使用 $ dateToString聚合运算符在$project聚合阶段内.

You may be able to use the $dateToString aggregation operator inside a $project aggregation stage.

例如:

> db.test.find()
{ "_id": 0, "date": ISODate("2018-03-07T05:14:13.063Z"), "a": 1, "b": 2 }

> db.test.aggregate([
    {$project: {
        date: {$dateToString: {
            format: '%d-%m-%Y %H:%M:%S',
            date: '$date'
        }},
        a: '$a',
        b: '$b'
    }}
])
{ "_id": 0, "date": "07-03-2018 05:14:13", "a": 1, "b": 2 }

请注意，尽管$dateToString运算符自MongoDB 3.0起可用，但MongoDB 3.6却增加了根据特定时区输出字符串的功能.

Note that although the $dateToString operator was available since MongoDB 3.0, MongoDB 3.6 adds the capability to output the string according to a specific timezone.

这篇关于使用Google表格"dd-MM-yyyy HH:ss"在MongoDB上查询ISODate.作为输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！