filtering a json set of data when dateformat is a string

Question

Suppose you have a json response string like this one, and you want to filter by date. You want to display records ONLY after a certain date.

[{btc=0, datetime=2018-01-30 12:16:11, eur=410.00, fee=0.00, usd=0, id=***, btc_usd=0.00, type=0}, {btc=0, eth_eur=692.81, datetime=2018-02-06 16:45:16, eur=-5.20, fee=0.02, usd=0, eth=0.00750000, id=***, type=2, order_id=***}, {btc=0, eth_eur=699, datetime=2018-02-05 17:04:25, eur=5.24, fee=0.02000000, usd=0, eth=-0.00750000, id=***, type=2, order_id=***}, {btc=0, eth_eur=562.1, datetime=2018-02-04 17:52:09, eur=-5.62, fee=0.02, usd=0, eth=0.01000000, id=***, type=2, order_id=***}, {btc=0, eth_eur=567.2, datetime=2018-02-06 20:24:07, eur=5.67, fee=0.02000000, usd=0, eth=-0.01000000, id=***, type=2, order_id=***}, {btc=0, datetime=2018-04-28 17:22:21, eur=0, fee=0.00000000, usd=0, eth=0.38811442, id=***, btc_usd=0.00, type=0}, {btc=0, eth_eur=563, datetime=2018-04-28 17:42:51, eur=218.50, fee=0.55000000, usd=0, eth=-0.38810000, id=***, type=2, order_id=***}, {btc=0, eth_eur=563, datetime=2018-04-28 17:47:52, eur=0.01, fee=0.01000000, usd=0, eth=-0.00001442, id=***, type=2, order_id=***}, {btc=0.01786568, btc_eur=5583.33, datetime=2018-08-14 12:01:13, eur=-99.75, fee=0.25, usd=0, id=***, type=2, order_id=***}]

In a similar case

{btc_available=0.01489932, eth_reserved=0.00000000, eur_balance=1858.63, btcusd_fee=0.500, xrpeur_fee=0.500, btc_balance=0.01489932, xrp_withdrawal_fee=0.02000000, ethusd_fee=0.500, ltceur_fee=0.500, eth_balance=1.30423351, xrp_reserved=0.00000000, bchusd_fee=0.500, eur_reserved=0.00, bch_available=0.00000000, usd_available=0.18, xrp_available=328.75000000, xrpusd_fee=0.500, ltcbtc_fee=0.500, bcheur_fee=0.500, ltc_available=0.00000000, btc_reserved=0.00000000, ltc_withdrawal_fee=0.00100000, usd_reserved=0.00, btc_withdrawal_fee=0.00050000, eurusd_fee=0.500, xrp_balance=328.75000000, ltcusd_fee=0.500, ltc_balance=0.00000000, bch_reserved=0.00000000, bch_withdrawal_fee=0.00010000, eur_available=1858.63, ltc_reserved=0.00000000, bchbtc_fee=0.500, ethbtc_fee=0.500, etheur_fee=0.500, usd_balance=0.18, eth_available=1.30423351, btceur_fee=0.500, eth_withdrawal_fee=0.00100000, bch_balance=0.00000000, xrpbtc_fee=0.500}

I could apply this filter:

var keys    = Object.keys(data);
var values  = Object.keys(data).map(function(e){return data[e]});

var result = [];
var k;
for (k=0; k<keys.length; k++){
  if (keys[k].slice(4, 13) == "available") {result.push([keys[k], values[k]]);}}

which I wasn't able to apply in this condition.

So I thought I would apply a for(){} case, but is there a more rational and efficient way to do this?

 var i;
  for (i=0; i < data.length; i++) {

   var d         = data[i].datetime;  
   var bits      = d.split(/\D/);
   var date      = new Date(bits[0], --bits[1], bits[2], bits[3], bits[4], bits[5]); 
   var date      = (date.getTime().toFixed(0))/1000;
   var date      = date.toString(); 

   if (date >  lastDate) { //IFFONE
   }
   }

I even considered trying to extract from the json string every pair of key/value set and render them as array, so that I could always point to the exact position (datetime in this case), but I guess there's a more immediate and consistent way. How would you do that? thanks
ps: I forgot to mentions that we are in google apps script

EDIT: outcome when applying @contributorpw's filter:

this script

var data  = {key: cred.key, signature: signature, nonce: nonce, sort: 'asc', limit: '10'};

var options = {'method' : 'post', 'muteHttpExceptions' : true, 'payload' : data};

var data    = UrlFetchApp.fetch('https://www.bitstamp.net/api/v2/user_transactions/', options);
var data    = JSON.parse(data.getContentText());
var values  = data;

produces the following set of json data:

[{btc=0, datetime=2018-01-31 12:15:11, eur=410.00, fee=0.00, usd=0, id=50575781, btc_usd=0.00, type=0},
{btc=0, eth_eur=692.81, datetime=2018-02-04 16:49:16, eur=-5.20, fee=0.02, usd=0, eth=0.00750000, id=52010995, type=2, order_id=893820507}, 
{btc=0, eth_eur=699, datetime=2018-02-04 17:04:25, eur=5.24, fee=0.02000000, usd=0, eth=-0.00750000, id=52013332, type=2, order_id=893880089}, 
{btc=0, eth_eur=562.1, datetime=2018-02-05 17:52:09, eur=-5.62, fee=0.02, usd=0, eth=0.01000000, id=52338326, type=2, order_id=900603492},
{btc=0, eth_eur=567.2, datetime=2018-02-05 20:29:07, eur=5.67, fee=0.02000000, usd=0, eth=-0.01000000, id=52444353, type=2, order_id=900621129}, 
{btc=0, datetime=2018-04-30 17:29:21, eur=0, fee=0.00000000, usd=0, eth=0.38811442, id=64286847, btc_usd=0.00, type=0},
{btc=0, eth_eur=563, datetime=2018-04-30 17:41:51, eur=218.50, fee=0.55000000, usd=0, eth=-0.38810000, id=64287296, type=2, order_id=1424216470}, 
{btc=0, eth_eur=563, datetime=2018-04-30 17:41:52, eur=0.01, fee=0.01000000, usd=0, eth=-0.00001442, id=64287298, type=2, order_id=1424216470}, 
{btc=0, datetime=2018-05-24 13:45:15, eur=1300.00, fee=0.00, usd=0, id=66875907, btc_usd=0.00, type=0}, 
{btc=0.01786568, btc_eur=5583.33, datetime=2018-08-12 12:01:13, eur=-99.75, fee=0.25, usd=0, id=72064184, type=2, order_id=1986400456}]

It's limited to only 10 records for semplicity.

Now, let's suppose we want to apply a filter that returns only data after the date 2018, 4, 16.
Here's what happens:

[19-12-17 12:08:48:897 PST] [
 {
  "fee": "0.00",
  "btc_usd": "0.00",
  "datetime": "2018-05-24 13:45:15",
  "usd": 0,
  "btc": 0,
  "type": "0",
  "id": 66875907,
  "eur": "1300.00"
 },
 {
  "fee": "0.25",
  "order_id": 1986400456,
  "datetime": "2018-08-12 12:01:13",
  "usd": 0,
  "btc": "0.01786568",
  "btc_eur": 5583.33,
  "type": "2",
  "id": 72064184,
  "eur": "-99.75"
 }
]

while instead you should have more records.. what do I do wrong?
here's the full script:

/* nuova funzione nonce */
_generateNonce = function() {
  var now = new Date().getTime();

  if(now !== this.last)
    this.nonceIncr = -1;

  this.last = now;
  this.nonceIncr++;

  // add padding to nonce incr
  var padding =
    this.nonceIncr < 10 ? '000' :
      this.nonceIncr < 100 ? '00' :
        this.nonceIncr < 1000 ?  '0' : '';
  return now + padding + this.nonceIncr;
} //fine funzione nonce

var nonce = this._generateNonce(); 

var cred = {
       id:'***',
      key:'***',
   secret:'***'};


var message = nonce + cred.id +  cred.key;

var res = Utilities.computeHmacSha256Signature(message, cred.secret).map(function(e) {return ("0" + (e < 0 ? e + 256 : e).toString(16)).slice(-2)}).join("");
var signature = res.toUpperCase();

  var data  = {key: cred.key, signature: signature, nonce: nonce, sort: 'asc', limit: '10'};

var options = {'method' : 'post', 'muteHttpExceptions' : true, 'payload' : data};

var data    = UrlFetchApp.fetch('https://www.bitstamp.net/api/v2/user_transactions/', options);
var data    = JSON.parse(data.getContentText());
var values  = data;

var date = new Date(2018, 4, 16).toISOString().split(/t/i)[0];

var filteredData = values.filter(function(item) {
  var d = item.datetime.split(/\s/)[0];
  return d >= date;});

Logger.log(JSON.stringify(filteredData, null, ' '));

2nd EDIT: what was the problem
The problem was simply that I had a one-month offset difference between the date I manually entered and the acutal date the script produced: so this messed up all the results and I couldnt understand why.

In other words, when entered manually, this line of command

var date = new Date(2018, 3, 28).toISOString().split(/t/i)[0];

Logger.log(date);

produces this outcome:

[19-12-18 13:54:17:932 CET] 2018-04-27

If, on the other hand, if you feed data straight from the cell (which is already date-formatted) nothing strange happens:

var date = aDate.toISOString().split(/t/i)[0];

var filteredData = values.filter(function(item) {
  var d = item.datetime.split(/\s/)[0];
  return d >= date;
});

Please read this for reference.
I have to say thanks to both @contributorpw and @Diego for their contribution. In the end I have chosen the answer from contributorpw because it's the one I've found more slender and agile on the code but both were good and functional. thanks

Answer 1

I'd like work with strings

var values = [{
    btc: 0,
    datetime: '2018-01-30 12:16:11',
    eur: 410.0,
    fee: 0.0,
    usd: 0,
    id: '***',
    btc_usd: 0.0,
    type: 0
  },
  ...
];
var date = new Date(2018, 3, 28).toISOString().split(/t/i)[0];
var filteredData = values.filter(function(item) {
  var d = item.datetime.split(/\s/)[0];
  return d >= date;
});
Logger.log(JSON.stringify(filteredData, null, ' '));

It works great.

If you'd like compare datetime then get date as

var date = new Date(2018, 3, 28, 17 + 5, 42)
  .toISOString()
  .split(/[t\.]/i)
  .slice(0, 2)
  .join(' ');

And compare this as

 var d = item.datetime.split(/\s/)[0];
 return d >= date;

You have consider the time shift 17 + 5.

Anytime you can skip date using

var filteredData = values.filter(function(item) {
  return item.datetime >= '2018-04-28';
});

Answer 2

Filtering requires comparison of values, in this case a specific date with the datetime property. As datetime is in an ISO 8601 format (though not the format supported by ECMA-262), the comparison can be with a string in the same format. This approach means generating one comparison string and then filtering without further processing of datetime.

Alternatively, the datetime strings can be converted to Dates, then the array filtered by comparing Date objects or time values (which are effectively the same thing). This may be less efficient as every datetime must be converted to a Date.

Both these issues have been covered before:

• Compare two dates with JavaScript
• Converting a string to a date in JavaScript and Why does Date.parse give incorrect results?

Date strings without a timezone should be treated as local. If you have separate information that they should be treated as UTC, that should be included in the question. Parsing should be done manually, do not trust the built–in parser (at least one current browser will parse '2018-01-31 12:15:11' as an invalid date).

Generating a date in the right format can use a simple formatting function, then compare to data[i].datetime. Similarly, converting datetime to a Date needs a simple parse function (or a library):

let data = [{datetime: '2018-01-31 12:15:11'},
            {datetime: '2018-02-04 16:49:16'}, 
            {datetime: '2018-02-04 17:04:25'}
           ];

// Filter as strings
function toISO(d) {
  let z = n => (n<10? '0':'') + n;
  return d.getFullYear() + '-' +
         z(d.getMonth() + 1) + '-' +
         z(d.getDate()) + ' ' +
         z(d.getHours()) + ':' +
         z(d.getMinutes()) + ':' +
         z(d.getSeconds());
}

let d = new Date(2018, 1, 1); // 1 Feb 2018
let filterDate = toISO(d);    // 2018-02-01 00:00:00
let filteredData_A = data.filter( o => o.datetime > filterDate);

console.log(filteredData_A);

// Filter as Date objects

function parseDate(s) {
  let b = s.split(/\D/);
  return new Date(b[0], b[1]-1, b[2], b[3], b[4], b[5]);
}

let filteredData_B = data.filter(o => parseDate(o.datetime) > d);

console.log(filteredData_B);

PS When posting, it's helpful to reduce code to the minimum required to demonstrate the issue and ensure it's valid (e.g. the "JSON" in the OP). See How to create a minimal, reproducible example.

Answer 3

You can easily convert that string into an ISO 8601 formatted date/time string and then create a new Date from it.

/**
 * Parse a datetime value formatted like "2018-01-30 12:16:11" into a JavaScript
 * Date object by converting it into a simple ISO 8601 formatted date time,
 * without a time zone designator. As such, the date returned is in the executing
 * machine's time zone (i.e. project time zone setting).
 * @param {String} datetime
 * @returns {Date}
 */
function parseDatetime(datetime) {
  var dateParts = datetime.split(" ");
  return new Date(dateParts.join("T"));
}

/**
 * Test that parseDatetime() works as expected.
 */
function test_parseDatetime() {
  var datetime = "2018-01-30 12:16:11";
  var date = parseDatetime(datetime);
  Logger.log(date); // Tue Jan 30 12:16:11 GMT+00:00 2018
}

Once you have your dates, you can use the comparison operators just as you wrote out.

Do please be aware that I had set the time zone in my project properties to "(GMT+00:00) GMT (no daylight saving)". The date returned by parseDatetime() will be affected by your project time zone settings. So long as you're consistent though, it shouldn't affect your comparisons.

Your final code could look something like this. I've included two options for filtering: (1) using a for loop and (2) using .filter(). You can see by checking the Logger output that they're equivalent. I also made a modification in this example to the parseDatetime() function to specify a UTC+0 time zone, to do address specific concerns brought up with Date.parse(), but I don't think it's entirely necessary.

function filterOrders() {
  var orders = [
    {btc:0, datetime:"2018-01-30 12:16:11", eur:410.00, fee:0.00, usd:0, id:"***", btc_usd:0.00, type:0},
    {btc:0, eth_eur:692.81, datetime:"2018-02-06 16:45:16", eur:-5.20, fee:0.02, usd:0, eth:0.00750000, id:"***", type:2, order_id:"***"},
    {btc:0, eth_eur:699, datetime:"2018-02-05 17:04:25", eur:5.24, fee:0.02000000, usd:0, eth:-0.00750000, id:"***", type:2, order_id:"***"},
    {btc:0, eth_eur:562.1, datetime:"2018-02-04 17:52:09", eur:-5.62, fee:0.02, usd:0, eth:0.01000000, id:"***", type:2, order_id:"***"},
    {btc:0, eth_eur:567.2, datetime:"2018-02-06 20:24:07", eur:5.67, fee:0.02000000, usd:0, eth:-0.01000000, id:"***", type:2, order_id:"***"},
    {btc:0, datetime:"2018-04-28 17:22:21", eur:0, fee:0.00000000, usd:0, eth:0.38811442, id:"***", btc_usd:0.00, type:0},
    {btc:0, eth_eur:563, datetime:"2018-04-28 17:42:51", eur:218.50, fee:0.55000000, usd:0, eth:-0.38810000, id:"***", type:2, order_id:"***"},
    {btc:0, eth_eur:563, datetime:"2018-04-28 17:47:52", eur:0.01, fee:0.01000000, usd:0, eth:-0.00001442, id:"***", type:2, order_id:"***"},
    {btc:0.01786568, btc_eur:5583.33, datetime:"2018-08-14 12:01:13", eur:-99.75, fee:0.25, usd:0, id:"***", type:2, order_id:"***"}
  ];

  // Select all orders where datetime is greater than April 1, 2018
  var filterDate = new Date("2018-04-01");

  // FOR loop method
  var filteredOrders1 = [];
  for (var i = 0; i < orders.length; i++) {
    var order = orders[i];
    if (parseDatetime(order.datetime) > filterDate) {
      filteredOrders1.push(order);
    }
  }

  // FILTER method
  var filteredOrders2 = orders.filter(function(order) {
    return parseDatetime(order.datetime) > this;
  }, filterDate);

  // Same results? 
  Logger.log(JSON.stringify(filteredOrders1) == JSON.stringify(filteredOrders2)); // true

  Logger.log(JSON.stringify(filteredOrders1));
}

/**
 * Parse a datetime value formatted like "2018-01-30 12:16:11" into a JavaScript
 * Date object by converting it into a simple ISO 8601 formatted date time, with
 * UTC as the time zone.
 * @param {String} datetime
 * @returns {Date}
 */
function parseDatetime(datetime) {
  var dateParts = datetime.split(" ");
  return new Date(dateParts.join("T") + "Z");
}