CODEWITHSUNDEEP
javascriptdatedatetimegoogle-apps-script
user1592380
user1592380

Reputation: 36367

Parsing datestring into timestamp correctly?

enter image description here

I'm working with apps script.

I'm parsing sheet rows into objects and I'm trying to filter them by time to get the the most recent entries in the last hour 

[{relativeRow=257.0, ME=, Index=false, FROM=1.xxx, absoluteRow=258.0, Timestamp=Fri Sep 20 14:17:50 GMT-04:00 2019, CONVERSATION=R:Yes!}, {relativeRow=256.0, ME=, Index=false, FROM=1xxx, absoluteRow=257.0, Timestamp=Fri Sep 20 12:18:00 GMT-04:00 2019, CONVERSATION=R:yes yes}, {relativeRow=255.0, ME=, Index=false, FROM=xxx, absoluteRow=256.0, Timestamp=Thu Sep 19 11:54:29 GMT-04:00 2019, CONVERSATION=R:Yes1}]

I have:

var MILLIS_PER_HOUR = 1000 * 60 * 60;  // 60 MINUTES
var now = new Date()
var oneHouragoTS = now.getTime() - MILLIS_PER_HOUR;
  Logger.log(oneHouragoTS);

var pendingRows =  descending.filter(function (row) {

    Logger.log(row['Timestamp']);

var rowDate = new Date(row['Timestamp'])
var rowTS = rowDate.getTime()

Logger.log('rowTS');
Logger.log(rowTS);
    Logger.log('oneHouragoTS');
Logger.log(oneHouragoTS);



var diff = rowTS - oneHouragoTS ;


Logger.log('diff');
Logger.log(diff);

return (diff>0) 

});

The output:

[19-09-20 15:40:54:016 EDT] getResponsesOverLastHour
[19-09-20 15:40:54:017 EDT] 1.569004854016E12
[19-09-20 15:40:54:017 EDT] Fri Sep 20 14:17:50 GMT-04:00 2019
[19-09-20 15:40:54:018 EDT] rowTS
[19-09-20 15:40:54:018 EDT] 1.56900347E12
[19-09-20 15:40:54:019 EDT] oneHouragoTS
[19-09-20 15:40:54:019 EDT] 1.569004854016E12
[19-09-20 15:40:54:020 EDT] diff
[19-09-20 15:40:54:020 EDT] -1384016.0
[19-09-20 15:40:54:020 EDT] Fri Sep 20 12:18:00 GMT-04:00 2019
[19-09-20 15:40:54:021 EDT] rowTS
[19-09-20 15:40:54:021 EDT] 1.56899628E12

I'm running this 30-40 minutes after the row was created, so I would assume that the diff would be positive and the row returned , however the diff is negative and filtering for rows created within 1 hour returns an empty array

How can I fix this?

Upvotes: 0

Views: 44

Answers (1)

some
some

Reputation: 49632

The date in your sheet is Fri Sep 20 14:17:50 GMT-04:00 2019

It can be parsed by the new Date("Fri Sep 20 14:17:50 GMT-04:00 2019")

console.log(new Date("Fri Sep 20 14:17:50 GMT-04:00 2019"))

Your calculation of one hour ago, is also correct:

var oneHourAgo = Date.now() - (60*60*1000); // shorter version

console.log(oneHourAgo, new Date(oneHourAgo));

But, you are not running it 30-40 minutes after it was created. You are running it 1 hour and 24 minutes later: 19-09-20 15:40:54:016 EDT.

Therefore you get negative values, since they are older than one hour.

May I suggest an optimization of your filter?

var now = Date.now();

var pendingRows =  descending.filter(function (row) {
  var rowTS = Date.parse(row['Timestamp']);
  var diff = now - rowTS;
  Logger.log(row['Timestamp'], rowTS, diff);
  return diff < (60*60*1000); 
});

Upvotes: 2

Related Questions