Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

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?

Maryna Brodina
Posts: 0
Joined: Thu Apr 05, 2012 7:27 am

Query (where clause) to select data based on a birthdate.

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

Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

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?

Maryna Brodina
Posts: 0
Joined: Thu Apr 05, 2012 7:27 am

Query (where clause) to select data based on a birthdate.

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

Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

var one_day = 3600000*24;

Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

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?

Maryna Brodina
Posts: 0
Joined: Thu Apr 05, 2012 7:27 am

Query (where clause) to select data based on a birthdate.

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

Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

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?

Kateryna Grynko
Posts: 0
Joined: Thu Nov 15, 2012 9:13 am

Query (where clause) to select data based on a birthdate.

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.

Dupdroid
Posts: 0
Joined: Sat Mar 30, 2013 9:07 pm

Query (where clause) to select data based on a birthdate.

Hi Katya,

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

Return to “Issues”