Weird time offset dealing with date/time and timezone

Question

I have been trying to code some functions to create google calendars and google calendar events based on information from multiple cells on a google spreadsheet.

First issues posted here with the date part has already been addressed.

Now I´m haing issues with the Time part.

The following code:

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Passeios");
  var timeStart = ss.getRange(6,4).getValue();
  var timeEnd = ss.getRange(6,5).getValue();
  var ssTZ = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

  Logger.log("timeStart: " + timeStart );
  Logger.log("timeEnd: " + timeEnd);


  var dateStart = ss.getRange(6,8).getValue();

  var dateStartObj = new Date(Utilities.formatDate(dateStart, 'ssTZ' , 'MMMM dd, yyyy HH:mm:ss Z'));

var timeStartObj= new Date(Utilities.formatDate(timeStart, 'ssTZ' , 'MMMM dd, yyyy HH:mm:ss Z'));

var justTimeStart = Utilities.formatDate(timeStart, ssTZ, 'HH:mm');


Logger.log(" Time Start Object: " + timeStartObj);
Logger.log("Time Start Object Hours: " + timeStartObj.getHours());
Logger.log("Time Start Object Minutes: " + timeStartObj.getMinutes());

Logger.log("Start Time HH:mm: " + justTimeStart)

var hourStart = Utilities.formatDate(timeStart, ssTZ, 'HH');
var minutesStart = Utilities.formatDate(timeStart, ssTZ, 'mm');
var hourEnd = Utilities.formatDate(timeEnd, ssTZ, 'HH');
var minutesEnd = Utilities.formatDate(timeEnd, ssTZ, 'mm');


Logger.log(" TimeZone :" + ssTZ);
Logger.log(hourStart);
Logger.log(minutesStart);
Logger.log(hourEnd);
Logger.log(minutesEnd);  

Produces the following log

timeStart: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

timeEnd: Sat Dec 30 1899 07:36:28 GMT-0300 (BRT)

Time Start Object: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

Time Start Object Hours: 7

Time Start Object Minutes: 6

Start Time HH:mm: 07:00

TimeZone :America/Sao_Paulo

07

00

07

30

The Spreadsheet cell is formatted as HH:mm and it shows 07:00 for start time 07:30 for end time

As you can see there is some 6 minutes and 28 seconds offset that I am not sure where is coming from when logging the cell value or when constructing a Date() object with the cell value.

Formating the cell to just Hours or just Minutes or HH:mm does not carry that offset.

EDIT.

I noticed that the Date() constructor had the ssTZ variable between single quote marks so it is probably discarded as it should not be recognized as a valid Timezone.

Not sure what it uses instead but the difference between the actual spreadsheet time zone and the misquoted one, seems to be 28 seconds that I also do not understand where they come from.

The 6 minutes offset is still there as you can check on the following code and log print.

  var timeStart = ss.getRange(6,4).getValue();
  var ssTZ = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

var timeStartObj1= new Date(Utilities.formatDate(timeStart, 'ssTZ' , 'MMMM dd, yyyy HH:mm:ss Z'));
var timeStartObj2= new Date(Utilities.formatDate(timeStart, ssTZ , 'MMMM dd, yyyy HH:mm:ss Z'));
var justTimeStart = Utilities.formatDate(timeStart, ssTZ, 'HH:mm');

  Logger.log("timeStart: " + timeStart );
  Logger.log(" Time Start Object1: " + timeStartObj1);
  Logger.log(" Time Start Object2: " + timeStartObj2);
  Logger.log("justTimeStart: " + justTimeStart)

timeStart: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

Time Start Object1: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

Time Start Object2: Sat Dec 30 1899 07:06:00 GMT-0300 (BRT)

justTimeStart: 07:00

EDIT 2

It has something to do with timezone and probably some adjustment due to the Date beign interpreted as 120 years ago in 1899.

When using the following constructor without the Z at the string specifying the format:

var timeStartObj3= new Date(Utilities.formatDate(timeStart, ssTZ , 'MMMM dd, yyyy HH:mm:ss'));
Logger.log(" Time Start Object3: " + timeStartObj3);

The log result is:

Time Start Object3: Sat Dec 30 1899 07:00:00 GMT-0300 (BRT)

EDIT 3.

Getting weirder and weirder...

If I use text concatenation on the Logger.log call I get a different String that if I call the log with just the var name:

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Passeios");
  var timeStart = ss.getRange(6,4).getValue();
  Logger.log("timeStart: " + timeStart);
  Logger.log(timeStart);

timeStart: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

Sat Dec 30 07:00:00 GMT-03:06 1899

I do understand it is in fact the same Time represented differently (I asume the 28 seconds are there even when not shown).

My guess is there's different behaviour from the text parsing method whether concatenation is used or not (which is at least confusing).

I still do not know where those 06 minutes and 28 seconds come from or how to ensure consistency when using Time and date coming from cell values with just date or just time and having to mix them.

This is really confusing...

Answer 1

The 6 minutes and 28 seconds come from the Local Mean Time (LMT) offset between São Paulo and GMT. You can see it in the TZDB sources:

# Zone  NAME               STDOFF    RULES   FORMAT   [UNTIL]
Zone    America/Sao_Paulo  -3:06:28  -       LMT      1914
                           -3:00     Brazil  -03/-02  1963 Oct 23  0:00
                           -3:00     1:00    -02      1964
                           -3:00     Brazil  -03/-02

The LMT entry is in the first row. The last column (1914) is the "Until" date - meaning that in the TZDB, LMT is in use until 1914. After that, the next rule in the zone entry applies (-3:00).

LMT is calculated based on the longitude and latitude of the reference location. It has no bearing on timekeeping that may have been in use in the region at that time. In many cases with old dates, there's no historical information available to know how exactly time was kept that long ago.

In other words, your example date from 1899 is from a period before known timekeeping practices in Brazil, and thus local mean time is applied instead.

Use a more modern date and you should get results that make more sense to you by today's standards.

Answer 2

Google sheets stores a Date or time cell value as a number corresponding to the number of full days (or fractions) starting 12/30/1899 0:00:00 as explained here.

When dealing with a cell that only contains a Time value, the getValues() function will produce a Date object with the date part set to 12/30/1899 but this will have a corresponding timezone offset which in time creates that odd offset as explained by @Matt Johnson on previous answer

The big problem is that this offset will not be consistent for diferent timezones as that date will produce different offsets for different locations/timezones and different years, so you will need to address a whole bunch of possible situations.

When trying to create Date() objects, this produces different results as it seems sometimes that offset is passed as a timezone offset and other times it is part of the actual time value depending on the constructor and format the text gets parsed to, creating a lot of confusion, as you can see from this code:

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Passeios");
var ssTZ = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var timeStart = ss.getRange(6,4).getValue();

var date1 = new Date(timeStart);
var date2 = new Date(Utilities.formatDate(timeStart, ssTZ , 'MMMM dd, yyyy HH:mm:ss Z'));
var date3 = new Date(Utilities.formatDate(timeStart, ssTZ , 'MMMM dd, yyyy HH:mm:ss'));

var date4 = new Date(Utilities.formatDate(timeStart, ssTZ , "yyyy-MM-dd'T'HH:mm:ss'Z'"));
     
  
Logger.log("date1: " + date1);
Logger.log("date2: " + date2);
Logger.log("date3: " + date3);
Logger.log("date4: " + date4);

Producing this log:

date1: Sat Dec 30 1899 07:06:28 GMT-0300 (BRT)

date2: Sat Dec 30 1899 07:06:00 GMT-0300 (BRT)

date3: Sat Dec 30 1899 07:00:00 GMT-0300 (BRT)

date4: Sat Dec 30 1899 04:00:00 GMT-0300 (BRT)

Sometimes the timezone offset is ignored alltogether, other times, only the seconds part is ignored and also it might be interpreted as a completely different timezone by hours. (That string format was taken from the formatDate() class documentation example)

As @TheMaster suggested, this might be addressed at the spreadsheet creating auxiliary cells/column on the sheet to add a more consistent date part but this might not be practical in cases where this information is dynamically processed (in my case a combination of multiple QUERY results across multiple answers from multiple associated forms).

Another approach also suggested by @TheMaster might be to use getDisplayValues() and parse the text but this might create all sort of trouble if the display format is ever changed in the sheet.

There doesn't seem to be a definitive answer

I think the least messy approach to this problem will be to use numeric variables in the code and parse numeric values just for the Hours and minutes using Utilities.formatDate() with the format property set just to 'HH' and 'mm' respectively.

var hours = Utilities.formatDate(timeStart, ssTZ, 'HH');
var minutes = Utilities.formatDate(timeStart, ssTZ, 'mm');

Logger.log("Hours: "+ hours);
Logger.log("Minutes: "+ minutes);

Gives the following result:

Hours: 07

Minutes: 00

This seems to be consistent and it disregards the timezone associated offsets and will produce a simple number for the hour and a simple number for the minutes corresponding to de displayed values at the sheet, which can then be used consistently to create Date() objects.

Note that the cell on the spreadsheet still needs to be formatted as Date/time for this to work but different from parsing text from a getDisplayValues() result, any date/time format will still work the same.

I'm not interested in the seconds or milliseconds but I suspect those could be addressed the same way.

Hopefully this helps someone else along the way.