Search dataset between two dates

Ask the community
,
1

If I would input two dates, how can I get the results between those dates from the dataset and display it to a table?

The date is on one field of the dataset and I would like to display only 5 fields on the table (5 columns) instead of all fields on the dataset.

2

You can set a filter using .ge() for the start date and .le() for the end date. The filter will result in all items between (and including) both of those dates. Something like this:

import wixData from 'wix-data';

// ...

wixData.query("myCollection")
  .ge("date", startDate) // you need to set startDate
  .le("date", endDate)   // you need to set endDate
  .find()
  .then( (results) => {
    if(results.items.length > 0) {
        // do stuff with the results
    } else {
      // handle case where no matching items found
    }
  } )
  .catch( (error) => {
    let errorMsg = error.message;
    let code = error.code;
  } );
1 Like
3

I add this one and it didn’t work, I don’t know how to filter the dataset after the query.

if(results.items.length > 0) {
$w('#manuListDataset').setFilter();
}
4

Hi @yisrael-wix

I was wondering if you would be able to help with the code above but for multiple fields?

i.e.

wixData.query("myCollection")
  .ge("date", startDate).or("dateTwo, startDate).or("dateThree", startDate)
  .le("date", endDate).or("dateTwo", startDate).or("dateThree", startDate)  
  .find()
  .then( (results) => {
    if(results.items.length > 0) {
        // do stuff with the results
    } else {
      // handle case where no matching items found
    }
  } )
  .catch( (error) => {
    let errorMsg = error.message;
    let code = error.code;
  } );

Thank you and I hope you keeping safe and well :slight_smile:

5

@stephenmccall Not really clear what you are trying to do, and why, but you should look at the .or() API docs for the correct usage of .or() .

6

Hi @yisrael-wix ,

Thanks for the info, I did a little reading up over the past 3 days and with a little help managed to get this working. I just have a slight issue with some code (related) that I’m working through at the moment. Thanks again and here is the code for anyone who wants to search BETWEEN two dates via a variety of fields in the database.

I presume there must be a slicker way Yisreal? I haven’t put all the code but I’m actually queering 12 date fields which seems inefficient but after 3 days it’ll do just fine!

let startDate = $w('#datePicker1').value;
let endDate = $w('#datePicker2').value;

let yearValue = startDate.getFullYear();
let monthValue = startDate.getMonth();
let dayValue = startDate.getDate();
let startDateSrch = new Date(yearValue, monthValue, dayValue, 0, 0, 0);

yearValue = endDate.getFullYear();
monthValue = endDate.getMonth();
dayValue = endDate.getDate();
let endDateSrch = new Date(yearValue, monthValue, dayValue, 23, 59, 59);

wixData.query("CourseItems")
.between('dateOne', startDateSrch, endDateSrch)
.or(wixData.query().between('dateTwo', startDateSrch, endDateSrch))
.or(wixData.query().between('dateThree', startDateSrch, endDateSrch))
.find()
  .then( (results) => {
    if(results.items.length > 0) {
        // do stuff with the results
    } else {
      // handle case where no matching items found
    }
  } )
  .catch( (error) => {
    let errorMsg = error.message;
    let code = error.code;
  } );
7

Does this work querying the collections _createdDate field ?