I have a script that I like to check when the script last ran. I thus log the "last run" datetime in a sheet before the script ends, and pull the data everytime the script starts. However, the calculation is always wrong when I compare the current date to the last ran date.
var dtToday = new Date();
var dtDebug=new Date(shtYahooDataRef.getRange(2,2).getValue()); //This is extra code for my debugging
var dtLastUpdate=new Date(shtYahooDataRef.getRange(1,2).getValue()); //This is where I retrieve the last ran datetime
// Just to check that the dates are correct
Logger.log (Utilities.formatDate(dtToday, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtDebug, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtLastUpdate, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
//Given that dtToday and dtDebug are very close, I expect the below to be very close but this is not case. Hence I wonder why
Logger.log (dtToday-dtLastUpdate);
Logger.log (dtDebug-dtLastUpdate);
Here is the log
December 07, 2021 02:26:25 +0800
December 07, 2021 02:26:13 +0800
December 07, 2021 02:25:13 +0800
4.3272544E7
60000.0
How is it that dtToday-dtLastUpdate and dtDebug-dtLastUpdate can be so different? Won't dtToday-dtLastUpdate be in the ball-park of 60000?
Additional info. This is the actual code where I exit the script if the last ran time was less than 6 hours ago. The condition was always false.
var t1 = dtToday.getTime(),
t2 = dtLastUpdate.getTime();
if ( Math.floor((t1-t2)/(3600*1000))< 6) {
return;
}; // 3600*1000 is milliseconds in an hour Update only is last update was 6 hr ago
Additional comment: If I have a simple script
var dtToday = new Date();
Logger.log (Utilities.formatDate(dtToday, "Asia/Hong_Kong", 'MMMM dd, yyyy hh:mm:ss Z')+" "+Session.getScriptTimeZone());
and the output is
1:57:21 PM Info December 10, 2021 01:57:21 +0800 Asia/Hong_Kong
How is it that running the script at 1:57PM returns 01:57:21 +0800 ? Shouldn't it be 13:57:21 +0800?
I found the issue. I log the time wrongly.
Instead of using 'MMMM dd, yyyy hh:mm:ss Z', I should have used 'MMMM dd, yyyy HH:mm:ss Z'
You can not directly "minus" a date from another date.
You have to first convert the date into millisecond. Then you can substract one date from another and then you have to convert the difference into minutes or hours.
var dtToday = new Date();
var dtDebug=new Date(shtYahooDataRef.getRange(2,2).getValue()); //This is extra code for my debugging
var dtLastUpdate=new Date(shtYahooDataRef.getRange(1,2).getValue()); //This is where I retrieve the last ran datetime
// Just to check that the dates are correct
Logger.log (Utilities.formatDate(dtToday, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtDebug, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
Logger.log (Utilities.formatDate(dtLastUpdate, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
//Given that dtToday and dtDebug are very close, I expect the below to be very close but this is not case. Hence I wonder why
Logger.log (dtToday.valueOf()-dtLastUpdate.valueOf());
Logger.log (dtDebug.valueOf()-dtLastUpdate.valueOf());