How can I convert unixtime stamp to a human readable date while query?

{
  "collection": "orders",
  "aggregate": [
    {
      "$project": {
        "date": "$ts_create"
      }
    }
  ],
  "fields": {
    "custom_order_id": 1,
    "date": 2,
    "total_amount": 3
  }
}

Welcome to the forum. Just add the $toDate aggregation to your pipeline. Like this:

{
  "collection": "orders",
  "aggregate": [
    {
      "$project": {
        "date": "$ts_create"
      },
      "$toDate": "date"
    }
  ],
  "fields": {
    "custom_order_id": 1,
    "date": 2,
    "total_amount": 3
  }
}

This confuses me. Does the "$ts_create" operator work for you? I can’t find any reference to it online.

“ts_create” is the column name in my collection which has timestamp.

But the query is still giving me following error:
Error running query: A pipeline stage specification object must contain exactly one field.

That error is from Mongo. You can check their docs for more info.

I can be more help if you share the query that produces that error.

Here is the query

    {
      "collection": "orders",
      "aggregate": [
        {
          "$project": {
            "date": "$ts_create"
          }
        }
      ],
      "fields": {
        "custom_order_id": 1,
        "date": 2,
        "total_amount": 3
      }
    }

I’m quite confused now. You haven’t changed any part of your query since you asked the initial question. Did you not use the $toDate aggregation?

I think the problem here is $ts_create. Try removing the dollar sign ($).

OK, I think “$toDate” is not available in redash query yet.
Here is my updated code:
{
“collection”: “orders”,
“aggregate”: [
{
“$project”: {“date”: {"$toDate": “$ts_create”}}
}
],
“fields”: {
“custom_order_id”: 1,
“date”: 2,
“total_amount”: 3
}
}

I am getting this error now:
Error running query: Unrecognized expression '$toDate’

I tried without sign in ts_create, I am getting same errors. I think ts_create will have to be with sign, coz it’s the field name which will have to get all the value from all documents

I was trying to redash for analytics, but looks like the community and docs are not so good to resolve any issues that come up for newbies. I think only option left is to use charting libraries in js to build dashboards from scratch.

The problem here is your MongoDB syntax. You tried to layer multiple aggregations inside one another, which MongoDB does not support. Try the example I provided above and report back if it throws errors.

If you can identify specific omissions in the docs we can certainly try to improve them.

As a side notes: analytics is a strong point for Redash. We have many long-time MongoDB users. So I’m sure, with time, we can get you up and running :muscle: