Time zones in Google Apps Script

Question

I want to convert a date object into a "serial number" that is consistent with the underlying value of dates used in Google Spreadsheets. My understanding is that in Javascript the primitive value of a date object is the number of milliseconds after (or before) midnight on 1st Jan, 1970; in Google Spreadsheets, the primitive value of a date is 0 (zero) for midnight on 30th Dec, 1899, and each day thereafter equals 1 (so, for example, 7 hours is equal to 7 divided by 24).

I tried this function:

function date2Serial(date) {
  return (date.getTime() + 2209161600000) / 86400000;
  // 2209161600000 = milliseconds between 1899-12-30 and 1970-01-01
  // 86400000 = milliseconds in a day
  // question "on the side" - is there any essential difference between
  //  .getTime() and .valueOf()?
}

And this returns a value that is a bit off - and I think the "off" corresponds with my time zone difference from GMT. So even though both the Spreadsheet Settings and Project Properties are set to GMT+10 in my case, it seems that getTime() operates on GMT.

So for example:

A1: 16/09/2012 06:00:00
A2: =date2serial(A1)
A3: =VALUE(A1)

A2 returns 41167.8333, where A3 returns 41168.25.

Now I tried using the getTimezoneOffset() method, which appeared to correct the issue:

function date2Serial(date) {
  return (date.getTime() + 2209161600000 - (date.getTimezoneOffset() * 60000)) / 86400000;
}

However this relies on the Project Properties time zone being correct, as opposed to the Spreadsheet Settings time zone (it is actually possible to set each of these differently).

My question:

Is this behaviour expected, and the "adjustment" for time zone necessary? Or should the getTime() (and all the other methods like getHours(), getMinutes() and so on) operate on the user's set time zone rather than GMT/UTC, and the "adjustment" is actually a workaround for a bug?

score 2 · Accepted Answer · answered Sep 17 '12 at 07:19

2

getTime() returns a time duration, not a moment in time and therefore does not have a time zone. The reference moment to substract is midnight of January 1, 1970 (UTC).

Methods like getHours(), getMinutes() and so on give part of a moment in time. The result is dependent on the time zone of the date object used. If you want to be independent of the date objects' time zone you should use getUTCHours(), getUTCMinutes() and so on.

answered Sep 17 '12 at 07:19

Taras

1,023
8
17

Thanks +Taras, that was very helpful. I realise now that getTime() will be zero on midnight of 1st Jan 1970 _UTC_, where in Google Spreadsheets a value of zero corresponds with midnight of 30th December 1899 _in whatever time zone the spreadsheet is set at_ - an essential difference. – AdamL Sep 17 '12 at 22:25

score 0 · Answer 2 · answered Jun 07 '20 at 11:59

This code will take into account local summertime as well. It is a bit inefficient because to be correct a Date object has to be created twice.

function ValueToDate(GoogleDateValue) {
  var d = Date.UTC(1899,11,30,0,0,0)+(GoogleDateValue*86400000) ;
  return new Date(d+(new Date(d)).getTimezoneOffset()*60000) ;
}

function DateToValue(date) {
  return (date.getTime()-date.getTimezoneOffset()*60000)/86400000+25569 ;
}

Time zones in Google Apps Script

2 Answers2

Linked