Select data from this month to 2 month ago

SQL++
1

Hi,
i want to select data from this month to 2 month ago,
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”
}
{
“_id”:“4”,
“_type”:“student”,
“name”:“kipli”,
“created”:“2018-07-04T06:46:03+07:00”,
“last_update”:“2018-07-04T06:46:03+07:00”,
“class”:“1”
}
{
“_id”:“5”,
“_type”:“student”,
“name”:“nusul”,
“created”:“2018-06-04T06:46:03+07:00”,
“last_update”:“2018-06-04T06:46:03+07:00”,
“class”:“1”
}
{
“_id”:“6”,
“_type”:“student”,
“name”:“ajib”,
“created”:“2018-05-04T06:46:03+07:00”,
“last_update”:“2018-05-04T06:46:03+07:00”,
“class”:“1”
}
i want select data student where new student who created from this month to 2 month ago i want the result like this:
{
“month”:“09”,
“count”:“2”
},
{
“month”:“08”,
“count”:“1”
},
{
“month”:“07”,
“count”:“1”
}

if there is anyone know how to make this, please help me.

THANKS

2

Hi,

The first challenge is to extract the month from the date “created”. For that I don’t know, you probably will find the answer in the doc.

After that, thinking sql, it’s just a select whith a count and a group by :

SELECT month, count(*) from belejar where created between date1 and date2 group by month

Of course you will have to replace “month” by the right convert fonction of the created date.

Steeve

3

Hello,

You can do it with the following query:

SELECT DATE_PART_STR(created, ‘month’) AS month, COUNT(*) as total
FROM TEST
WHERE
_type=“student” AND
DATE_PART_STR(created, ‘month’) >= DATE_PART_STR(CLOCK_STR(), ‘month’) - 2
GROUP BY DATE_PART_STR(created, ‘month’)

This index will make it run super-fast:

CREATE INDEX idx_month_creation ON TEST(DATE_PART_STR(created, ‘month’)) WHERE _type=“student”

Manuel

2 Likes
4

thanks that’s it help me much