Forum | Appery.io

I've got a collection : person with a column: birthdate

How can I query (construct the where clause in JavaScript) to select the people whose birthday is coming up in the next 30 days?

Hello! Column "birthdate" should have date type. Also these links should help https://getsatisfaction.com/apperyio/...
http://docs.appery.io/documentation/b...

Hi Marina,

The 'birthdate' column is a date type. I know how to write simple date queries from looking at your documentation. The problem is that the "birthdate" is obviously in a different year so I need to compare the month and day part to determine the difference of 30 days from any given date. Is there a way to construct a where clause that will do this? Hope that makes sense?

It's not that easy to do such request. As an option you can try to add a column where you save days and while saving select records where days number starting from the beginning of the year is +-30 from the amount of days starting from the beginning of the year for current date. To be precise you can also take into account leap year. To retreive number of days from the beginning of the year you can use the following function:
codefunction numDays(dateStr) {
//dateStr date in format YYYY-MM-DD. if not specified - use current date
var day = dateStr ? new Date(dateStr) : new Date();
var start = new Date(day.getFullYear(), 0, 1);
var one_day = 10006060*24;
return (Math.ceil((day.getTime()-start.getTime())/(one_day)));
}/code

var one_day = 3600000*24;

Thank you Marina,

I am using your suggested function to populate a column called "birthdayinyear".

I'm now trying to construct a where clause to select all the records where the birthdayinyear in within a certain range.

I have tried:

{"birthdayinyear":{"$gt":100}} AND {"birthdayinyear":{"$lt":200}}

But this doesn't seem to work because it returns rows where the values are larger than 200 but not less than.

I have also tried: (with the extra brackets on the outside}

{{"birthdayinyear":{"$gt":100}} AND {"birthdayinyear":{"$lt":200}}}

But no records are returned.

Once this is fixed I still need to adapt the query to return records where birthdays are in January and the query is executed in December.

I hope this makes sense?

Hello! Try to use codevar one_day = 1000606024;/code instead of codevar one_day = 1000606024;/code

Thank you Marina,

I am using your suggested function to populate a column called "birthdayinyear".

I'm now trying to construct a where clause to select all the records where the birthdayinyear in within a certain range.

I have tried:

{"birthdayinyear":{"$gt":100}} AND {"birthdayinyear":{"$lt":200}}

But this doesn't seem to work because it returns rows where the values are larger than 200 but not less than.

I have also tried: (with the extra brackets on the outside}

{{"birthdayinyear":{"$gt":100}} AND {"birthdayinyear":{"$lt":200}}}

But no records are returned.

Once this is fixed I still need to adapt the query to return records where birthdays are in January and the query is executed in December.

I hope this makes sense?

Hi Christo,

The request with logical AND:
code{"birthdayinyear":{"$gt":100, "$lt":200}}/code
The request with logical OR:
code{"$or":[{"birthdayinyear":{"$gt":120}}, {"birthdayinyear":{"$lt":200}}]}/code
[quote:]I still need to adapt the query to return records where birthdays are in January and the query is executed in December[/quote]Add the following code to "where" mapping:
codefunction numDays(dateStr) {
//dateStr date in format YYYY-MM-DD. if not specified - use current date
var day = dateStr ? new Date(dateStr) : new Date();
var start = new Date(day.getFullYear(), 0, 1);
var one_day = 1000 * 60 * 60 * 24;
return (Math.ceil((day.getTime()-start.getTime())/(one_day)));
}
var currDays = numDays();
var beginDay = currDays - 30;
var endDay = currDays + 30;
if (beginDay < 0 ) {
return '{"$or":[{"birthdayinyear":{"$gt":' + (beginDay + 366) + '}}, {"birthdayinyear":{"$lt":' + endDay + '}}]} ';
}
if (endDay > 366 ) {
return '{"$or":[{"birthdayinyear":{"$gt":' + beginDay + '}}, {"birthdayinyear":{"$lt":' + (endDay - 366) + '}}]} ';
}
return '{"birthdayinyear":{"$gt":' + beginDay + ', "$lt":' + endDay + '}}';/codewhere 366 - are the number of days in leap year.

Hi Katya,

Wow, thanx very much for all your effort. I will give it a try.