处理日期/时间和时区时出现问题 [英] Problem dealing with date/time and timezone
问题描述
我正在基于链接到Google表单的Google表格中的信息在Google日历上创建活动系列。处理日期是一团糟,我得到的结果是不稳定的。
我在电子表格上有一个字段,其开始日期为 YYYY / MM / DD
我有一个字段,其开始时间显示为 HH:mm
我有一个字段的结束时间显示为 HH:mm
我了解内部时间为一年
我最终需要使用DATE的DATE部分创建日历事件系列。以及我可以使用 setHours()
setMinutes()
方法更改的时间中的时间部分。
现在,我的问题是从DATE单元格值创建一个一致的Date对象,因为它似乎发生了怪异的变化。
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(日期);
Logger.log(dateTZ);
Logger.log(dateSP);
Logger.log(dateOS);
该代码产生以下Log输出:
< blockquote>
[19-07-02 20:39:49:780 BRT]电子表格时区:America / Sao_Paulo
[19-07-02 20:39:49:781 BRT]周二1月12日00:00:00 GMT-02:00 2016
[19-07-02 20:39:49:782 BRT]
[19-07- 02 20:39:49:784 BRT]周二1月12日10:00:00 GMT-02:00 2016
[19-07-02 20:39:49:784 BRT]周二1月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] 2016年1月11日星期一10:00:00:00 GMT-02:00
这没有意义,因为使用的所有时区实际上都应该是相同的。
EDIT .... Addedinfo
按照@TheMaster的要求,来自文本格式变量的日志为:
Logger.log (dataStart);
Logger.log(dataStartTZ);
Logger.log(dataStartSP);
Logger.log(dataStartOS);
[19-07-03 12:39:33:099 BRT ] 2016年1月12日12:00:00 +0000
[BRT 19-19-03 12:39:33:100] 2016年1月12日12:00: 00 -0200
[19-07-03 12:39:33:100 BRT] 2016年1月12日12:00:00 -0200
[19-07-03 12:39:33:100 BRT] 2016年1月11日12:00:00 -0300
@ziganotscha解释的前3个输出是由于夏令时更改为GMT-2,而美国/巴西利亚没有被确认为有效时区。
我仍然不知道为什么将GMT-3指定为时区时它将更改为1月11日。
如果我直接从单元格值构建Date对象没有格式化,我得到另一个不同的时间:
var rawDate = new Date(SSDate);
19-07-03 12:39:33:101 BRT] rawDate:2016年1月12日星期二00:00:00 GMT-0200(BRST)
我讨厌处理日期...如此令人困惑...
编辑2 ...更多时间问题。
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(开始时间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);
产生以下日志
timeStart:1899年12月30日星期六07:06:28 GMT-0300(BRT)
timeEnd:1899年12月30日星期六07:36:28 GMT- 0300(BRT)
时间开始对象:1899年12月30日星期六GMT-0300(BRT)
时间开始对象时数:7
时间开始对象分钟数:6
开始时间HH:mm :07:00
TimeZone:美国/圣保罗
07
00
07
30
电子表格单元格的格式为HH:mm,显示开始时间
07:00
结束时间07:30
如您所见,在记录单元格值或使用单元格值构造Date()对象时,我不确定会有6分28秒的偏移量。
将单元格格式化为Hours或
因此,电子表格时间 SSDate
是
2016年1月12日星期二00:00:00 GMT-02:00
即1月12日从格林尼治标准时间-2小时开始于时区的午夜。
'美国/巴西利亚'
不是有效的时区,可以忽略。
两者都是 America / Sao_Paulo
和 ssTZ
作为 Utilities.formatDate()
的第二个参数,产生相同的有效日期
2016年1月12日00:00:00 -0200
当偏移量为 GMT-3
,距格林尼治标准时间午夜3时,或距格林尼治标准时间2时区-1小时,则日期为
2016年1月11日23:00: 00 -0300
即前一天的晚上11点。
问题:
您的日志忽略了日期的时间部分,因为您提供了静态时间格式r所有日期 12:00:00 Z
而不是 HH:mm:ss
。
例如,以 GMT-3
格式化的最后日期变为
2016年1月11日12:00:00 -0300
调用新的Date()会将上述对象更改为本地时间 GMT -2
(格林尼治标准时间3 +1):
2016年1月11日星期一10:00:00:00
请注意,强烈建议不要使用 new Date(timestring)
进行解析。
参考文献:
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.
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:
这篇关于处理日期/时间和时区时出现问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!