javascriptdategoogle-ads-script

(getTime() - 5 * 3600 * 1000) takes 3 hours back and not 5


I'm trying to query stats for the hour which as the time 5 hours ago.

When I run this code, I see in the logs that the local time is 6 so I expect pastHour to be 1, but it says 3.

Why?

enter image description here

var HOURS_BACK = 5;

function main() {

  var past = new Date(new Date().getTime() - HOURS_BACK * 3600 * 1000);
  var pastHour = past.getHours();
  var pastDateStr = getDateStringInTimeZone(past, 'yyyy-MM-dd');
  query = "SELECT customer.id, metrics.impressions, segments.hour FROM customer WHERE metrics.impressions = 0 AND segments.hour = " + pastHour + " AND segments.date = '" + pastDateStr + "'";
  Logger.log("query " + query);
  }

Solution

  • edit — I'll leave the content below, but I now think that the mystery of the OP is not explainable by what's posted in the question.


    It's important to keep in mind how Date values work. Internally, a Date is always a UTC-relative timestamp. That is, if (hypothetically) you and somebody across the world from you simultaneously called new Date().getTime(), you'd both get the same timestamp. (Obviously not really possible, but imagine you live in a cartoon world.)

    However, the Date APIs like .getHours() work in your local time. The difference between local time and universal time causes lots of people confusion all the time, and things only get worse when your date/time values are subsequently involved in database operations, where the same problems may crop up once again.