2

I am using alasql to get data between given date range from array of object in JavaScript. i tried below query but i am not getting any result. i am passing start date and end date and i want the data between those range only, if there is any other way to do this then it will help me also. please help me to do this, thanks in advance 

startTime = Sun Mar 27 2016 00:00:00 GMT+0530 (India Standard Time)
endTime = Sat Apr 02 2016 00:00:00 GMT+0530 (India Standard Time)

data= [
{
  name:'Sam',
  reportDate:'3/25/2016 16:34:00 PM'
},{
  name:'Mac',
  reportDate:'3/27/2016 10:26:00 PM'
},{
  name:'Sandy',
  reportDate:'3/30/2016 19:14:00 AM'
},{
  name:'Smith',
  reportDate:'4/2/2016 14:54:00 PM'
},{
  name:'Warner',
  reportDate:'4/4/2016 17:04:00 AM'
},{
  name:'Finch', 
  reportDate:'3/28/2016 11:20:00 PM'
}
]

alasql("SELECT * from ?   WHERE DATE([[0].reportDate]) >=" + startDate + "AND DATE([[0].reportDate]) <= " + endDate + "", [data]);
jcubic
  • 61,973
  • 54
  • 229
  • 402
Mahi Kalyankar
  • 257
  • 5
  • 18
  • Do you want to read out the values from this `array` with `js`? So that you get the `data` of **Mac, Sandy** and **Smith**? – John Apr 05 '16 at 09:51
  • @John i want result as a array of object in between given Dates. it should exclude the data which is not coming under date range – Mahi Kalyankar Apr 05 '16 at 09:55
  • Just to make sure: You have the above situation and want to get the datarange from the array now by javascript? The output should be the data of Mac, Sandy and Smith? – John Apr 05 '16 at 10:04
  • yes, you are right any data which comes under the date range , in this case it is Mac,Sandy,Finch and Smith – Mahi Kalyankar Apr 05 '16 at 10:12
  • yes, you are right any data which comes under the date range from **data array** , in this case it is Mac,Sandy,Finch and Smith – Mahi Kalyankar Apr 05 '16 at 10:21
  • You need to have timezone information on ALL your date/time data or avoid it totally. If its all in the same timezone I suggest you do the calculations without timezone. – mathiasrw Apr 29 '16 at 18:44

2 Answers2

2

Try this:

var startDate = '2016-03-27';
var endDate = '2016-04-02';

var data = [
{
  name:'Sam',
  reportDate:'3/25/2016 16:34:00 PM'
},{
  name:'Mac',
  reportDate:'3/27/2016 10:26:00 PM'
},{
  name:'Sandy',
  reportDate:'3/30/2016 07:14:00 AM'
},{
  name:'Smith',
  reportDate:'4/2/2016 02:54:00 PM'
},{
  name:'Warner',
  reportDate:'4/4/2016 05:04:00 PM'
},{
  name:'Finch', 
  reportDate:'3/28/2016 11:20:00 PM'
}
];
var result = alasql("SELECT * from ?   WHERE DATE(reportDate) >= DATE('" + startDate +"') AND DATE(reportDate) <= DATE('" + endDate + "')", [data]);
document.body.innerHTML = JSON.stringify(result);
<script src="https://cdnjs.cloudflare.com/ajax/libs/alasql/0.2.5/alasql.min.js"></script>
jcubic
  • 61,973
  • 54
  • 229
  • 402
  • Thank you. one thing is in your result it is not showing the result of smith and same is happening with my query also. i am not able to figure it out. here is my query ** alasql("SELECT * from ? where reportDate >= '" + startTime + "' AND reportDate <= '" + endTime + "' ", [data]); ** – Mahi Kalyankar Apr 05 '16 at 14:45
  • Smith doesnot come in result set because, the data has time. The query is correct! – Anoop Thiruonam Feb 03 '21 at 16:45
  • @Anoop.P.A you're not correct the problem is that the range data in OP code are just strings, they need to be converted to Date objects in order to compare them. OP code only convert dates from array, but not from range he want to select. – jcubic Feb 04 '21 at 17:30
0

This is how you can catch the data by javascript:
(It is only working if startTime and endTime are Strings.)

var month = ["Jan", "Feb", "Mar", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dec"];

startTime = "Sun Mar 27 2016 00:00:00 GMT+0530 (India Standard Time)"
endTime = "Sat Apr 02 2016 00:00:00 GMT+0530 (India Standard Time)"

data = [{
  name: 'Sam',
  reportDate: '3/25/2016 16:34:00 PM'
}, {
  name: 'Mac',
  reportDate: '3/27/2016 10:26:00 PM'
}, {
  name: 'Sandy',
  reportDate: '3/30/2016 19:14:00 AM'
}, {
  name: 'Smith',
  reportDate: '4/2/2016 14:54:00 PM'
}, {
  name: 'Warner',
  reportDate: '4/4/2016 17:04:00 AM'
}, {
  name: 'Finch',
  reportDate: '3/28/2016 11:20:00 PM'
}]

// get the startTime f.e. 3/27/2016
var startT = /\s(\w+)\s(\d+)\s(\d+)/.exec(startTime); // get month out of startTime
var mS = month.indexOf(startT[1]) + 1; // get month & exchange month into digit
var dS = /0?(\d+)/.exec(startT[2])[1]; // get rid of possible "0" from day
var yS = startT[3];
var startTime_ = mS + "/" + dS + "/" + yS;

// get the endTime
var endT = /\s(\w+)\s(\d+)\s(\d+)/.exec(endTime); // get month out of startTime
var mE = month.indexOf(endT[1]) + 1; // get month & exchange month into digit
var dE = /0?(\d+)/.exec(endT[2])[1]; // get rid of possible "0" from day
var yE = endT[3];
var endTime_ = mE + "/" + dE + "/" + yE;

var startIndex = 0;
var endIndex = 0;

// get startIndex from data-array
data.forEach(function(e, i) {
  if (e.reportDate.indexOf(startTime_) >= 0) {
    startIndex = i; // this is where we start to get data from date-array
  }
})

// get endIndex from data-array
data.forEach(function(e, i) {
  if (e.reportDate.indexOf(endTime_) >= 0) {
    endIndex = i; // this is where we end to get data from date-array
  }
})

for (var i=startIndex; i<=endIndex; i++){
  document.write("name: "+data[i].name+"<br/>reportDate: "+data[i].reportDate+"<br/><br/>"); //data[i] is the data you want
}
John
  • 760
  • 5
  • 12