N1QL convert timezone into date,Couchbase

Question

I want to convert 2015-10-29T16:15:11.000Z in to 2015-10-29 Date in N1QL query. How can I do it ?

Answer 1

There are a couple of options here. The simplest way works if you can tolerate an ISO 8601 formatted time and uses the DATE_TRUNC_STR(expression, part) Date function:

SELECT DATE_TRUNC_STR(date_time, "day") AS new_date FROM bucket

The result for such a query (using 2015-10-29T16:15:11.000Z as date_time) is:

{
  "results": [
    {
      "new_date": "2015-10-29T00:00:00Z"
    }
  ]
}

---

Alternatively, you can parse out the individual parts using the DATE_PART_STR(expression, part) function with the TOSTRING(expression) function with String concatenation (||):

SELECT TOSTRING(DATE_PART_STR(date_time, "year")) || "-" || TOSTRING(DATE_PART_STR(date_time, "month")) || "-" || TOSTRING(DATE_PART_STR(date_time, "day")) AS new_date FROM bucket

The result:

{
  "results": [
    {
      "new_date": "2015-10-29"
    }
  ]
}

---

Reference: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html