Problem dealing with date/time and timezone

Question

I'm creating event series on a Google Calendar based on information from Google Sheets linked to a Google Form. Dealing with dates is a mess and I'm getting erratic results.

I have a field on the spreadsheet with a start DATE shown as YYYY/MM/DD
I have a field with a start TIME shown as HH:mm
I have a field with an end TIME shown as HH:mm

I understand that internally the time has an year/month/day associated and the DATE also has a time associated that are not shown.

I will eventually need to create a calendar event series using the DATE part from the DATE and the TIME part from the time which I can change using setHours() setMinutes() methods.

Now my problem is creating a consistent Date object from the DATE cell Value as it seems to change weirdly.

var ssTZ = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var SSDate = ss.getRange(6,8).getValue();
var dataStart = Utilities.formatDate(SSDate, 'America/Brasilia' , 'MMMM dd, yyyy 12:00:00 Z');
var dataStartTZ = Utilities.formatDate(SSDate, ssTZ , 'MMMM dd, yyyy 12:00:00 Z');
var dataStartSP = Utilities.formatDate(SSDate, 'America/Sao_Paulo' , 'MMMM dd, yyyy 12:00:00 Z');
var dataStartOS = Utilities.formatDate(SSDate, 'GMT-3' , 'MMMM dd, yyyy 12:00:00 Z');

var date = new Date(dataStart);
var dateTZ = new Date(dataStartTZ);
var dateSP = new Date(dataStartSP);
var dateOS = new Date(dataStartOS);  

Logger.log("Spreadsheet TimeZone: " + ssTZ);
Logger.log(SSDate);
Logger.log("");
Logger.log(date);  
Logger.log(dateTZ);
Logger.log(dateSP);
Logger.log(dateOS);

That code produces the following Log outputs:

[19-07-02 20:39:49:780 BRT] Spreadsheet TimeZone: America/Sao_Paulo
[19-07-02 20:39:49:781 BRT] Tue Jan 12 00:00:00 GMT-02:00 2016
[19-07-02 20:39:49:782 BRT]
[19-07-02 20:39:49:784 BRT] Tue Jan 12 10:00:00 GMT-02:00 2016
[19-07-02 20:39:49:784 BRT] Tue Jan 12 12:00:00 GMT-02:00 2016
[19-07-02 20:39:49:785 BRT] Tue Jan 12 12:00:00 GMT-02:00 2016
[19-07-02 20:39:49:786 BRT] Mon Jan 11 13:00:00 GMT-02:00 2016

which don't make sense since all the timezones used should actually be the same one.

EDIT.... Addedinfo

As requested by @TheMaster the logs from the Text formatted variables are:

  Logger.log(dataStart);  
  Logger.log(dataStartTZ);
  Logger.log(dataStartSP);
  Logger.log(dataStartOS);

[19-07-03 12:39:33:099 BRT] January 12, 2016 12:00:00 +0000

[19-07-03 12:39:33:100 BRT] January 12, 2016 12:00:00 -0200

[19-07-03 12:39:33:100 BRT] January 12, 2016 12:00:00 -0200

[19-07-03 12:39:33:100 BRT] January 11, 2016 12:00:00 -0300

The first 3 outputs as explained by @ziganotscha are due to summer time change to GMT-2 and America/Brasilia not beign recognized as valid timezone.

I'm still puzzled aout why it changes to January 11 when GMT-3 specified as Timezone.

Furthermore if i build the Date object directly from the cell value without formatting I get another diferent time:

var rawDate = new Date(SSDate);

19-07-03 12:39:33:101 BRT] rawDate: Tue Jan 12 2016 00:00:00 GMT-0200 (BRST)

I hate dealing with Dates... so confusing...

EDIT 2 ... More issues with time.

  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.

Log `SSDate, dataStart,dataStartTZ, dataStartSP,dataStartOS` instead — TheMaster, Jul 03 '19 at 06:50
This is the log for those:Logger.log(dataStart); Logger.log(dataStartTZ); Logger.log(dataStartSP); Logger.log(dataStartOS); 19-07-03 12:27:40:438 BRT] January 12, 2016 12:00:00 +0000 [19-07-03 12:27:40:439 BRT] January 12, 2016 12:00:00 -0200 [19-07-03 12:27:40:440 BRT] January 12, 2016 12:00:00 -0200 [19-07-03 12:27:40:440 BRT] January 11, 2016 12:00:00 -0300 — Pablo Delbracio, Jul 03 '19 at 15:29
My script timezone is (GMT-03:00) Sao Paulo But I guess the date I'm using in january is set to GMT-02:00 because at that date summertime was in place and indeed Sao Paulo Time was GMT -02:00 at that moment — Pablo Delbracio, Jul 03 '19 at 22:03

score 1 · Answer 1 · answered Jul 03 '19 at 10:27

There is a confusion about time zones:

Your time zone is the 'America/Sao_Paulo' time zone - this is why Logger.log(dateTZ); and Logger.log(dateSP); give you the same time - as you expect.

However, keep in mind that in January Sao Paulo observes the Brasilia Summer Time (UTC−02 instead of UTC−03), This is where the difference between Logger.log(dateSP); and comes Logger.log(dateOS); from.

https://www.timeanddate.com/time/zones/brt

As for dataStart - you assign it the time zone 'America/Brasilia', which is not a defined name for a time zone. This is why Apps Script is not accepting the value and gives you UTC+0 timezone.

You can verify that formatDate() uses the time zone as specified by Java SimpleDateFormat: https://developers.google.com/apps-script/reference/utilities/utilities http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

And java uses the Olson Timezone Database.

https://www.oracle.com/technetwork/java/javase/dst-faq-138158.html#worldwide https://en.wikipedia.org/wiki/Time_in_Brazil

I though summertime was involved on the first 2, but I still do not understand why GMT-3 changes the day from Jan12 to Jan 11 @ 13:00 I also could not call getTimeZone() or setTimeZone() methods on google script, not sure why... I get "TypeError: Cannot find function getTimeZone in object" — Pablo Delbracio, Jul 03 '19 at 14:51
And... if I build the Date() from the original cell value without formatting i get a different time... var rawDate = new Date(SSDate); Logger.log("rawDate: " + rawDate); LOG OUTPUT: rawDate: Tue Jan 12 2016 00:00:00 GMT-0200 (BRST) — Pablo Delbracio, Jul 03 '19 at 15:40

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

So, Spreadsheet time SSDate is

Tue Jan 12 00:00:00 GMT-02:00 2016

i.e., Jan 12 start at midnight in timezone -2 hours from GMT.

'America/Brasilia' is not a valid timezone and can be ignored.

Both, America/Sao_Paulo and ssTZ as the second argument to Utilities.formatDate() produces the same valid date

January 12, 2016 00:00:00 -0200

When the offset is GMT-3, it is -3 hours from midnight GMT, or -1 hour from GMT-2 timezone, the date is

January 11, 2016 23:00:00 -0300

i.e., 11pm on the previous day.

Issue:

Your logs ignore the time part of date because you provided a static time format for all the dates 12:00:00 Z instead of HH:mm:ss.

So, for example, the last date formatted on GMT-3 becomes

January 11, 2016 12:00:00 -0300

Calling a new Date() changes the above object to local time GMT-2(+1 from GMT-3):

Mon Jan 11 13:00:00 GMT-02:00 2016

Note that parsing using new Date(timestring) is strongly discouraged.

References:

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 03 '19 at 16:39

TheMaster

45,448
6
62
85

That makes more sense... I had not realized that 12:00:00 instead of HH:mm:ss would produce such behaviour. I am indeed trying to use Date() constructors using individual component values but because I'm using dates and times from different cell values I needed to understand the erratic Date() behaviour I was getting. This helps a lot, thanks. – Pablo Delbracio Jul 03 '19 at 21:55
I'vejust updated the post with some other weird behaviour on the Time part this time. – Pablo Delbracio Jul 30 '19 at 23:45
@Pablo Kindly ask a new question – TheMaster Jul 31 '19 at 00:22
Just posted new question [here](https://stackoverflow.com/questions/57296496/weird-time-offset-dealing-with-date-time-and-timezone) – Pablo Delbracio Jul 31 '19 at 17:58

Problem dealing with date/time and timezone

2 Answers2

Issue:

References:

Linked