Select data for a month

SQL++
1

hi,
i want ask how to select data for a month
please help me

thanks

2

Check out date functions and use them. https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

If still need help please post sample document and criteria how to select data.

3

i have bucket name is belajar and document like this:
{
“_id”:“1”,
“_type”:“student”,
“name”:“ahad”,
“created”:“2018-09-02T06:46:03+07:00”,
“last_update”:“2018-09-02T06:46:03+07:00”,
“class”:“1”
},
{
“_id”:“2”,
“_type”:“student”,
“name”:“ijik”,
“created”:“2018-09-04T06:46:03+07:00”,
“last_update”:“2018-09-04T06:46:03+07:00”,
“class”:“1”
},
{
“_id”:“3”,
“_type”:“student”,
“name”:“panjul”,
“created”:“2018-08-04T06:46:03+07:00”,
“last_update”:“2018-08-04T06:46:03+07:00”,
“class”:“1”
}

i want select data student where new student who created for a month i want the result like this:
{
“_id”:“1”,
“_type”:“student”,
“name”:“ahad”,
“created”:“2018-09-02T06:46:03+07:00”,
“last_update”:“2018-09-02T06:46:03+07:00”,
“class”:“1”
},
{
“_id”:“2”,
“_type”:“student”,
“name”:“ijik”,
“created”:“2018-09-04T06:46:03+07:00”,
“last_update”:“2018-09-04T06:46:03+07:00”,
“class”:“1”
}

4 
CREATE INDEX ix1 ON belajar(created) WHERE _type = "student";

SELECT * FROM belajar
WHERE _type = "student" 
AND created BETWEEN DATE_ADD_STR(CLOCK_STR(),-1,"month") AND CLOCK_STR();

SELECT * FROM belajar
WHERE _type = "student" 
AND created >= "2018-09" AND created < "2018-10";

As date is stored ISO8601 format it is string comparable.

You can also replace CLOCK_STR() with actual string "2018-09-04T06:46:03+07:00"

If you are not interested on time

    CREATE INDEX ix1 ON belajar(SUBSTR(created,0,10)) WHERE _type = "student";

    SELECT * FROM belajar
    WHERE _type = "student" 
    AND SUBSTR(created,0,10) >= DATE_ADD_STR("2018-09-03",-1,"month") 
   AND SUBSTR(created,0,10) < "2018-09-03" ;
1 Like
5

thanks bro that is very help me.