从Google Sheet将可用日期写入MySQL

Question

我有一个脚本，将谷歌工作表中的数据写入谷歌云MySQL表，但希望有可供data Studio使用的日期。我尝试过使用日期、日期时间和时间戳，但我得到了相同的错误：

尝试更新DBTestData中的TABLEsampledata，但返回以下错误：异常：数据截断：错误的日期时间值：第1行中"dateordered"列的"Tue Jan 01 00:00:00 GMT-0500(东部标准时间(">

我试过在Data Studio中使用ToDate函数将其更改为日期，但它仍然无法识别。我觉得我的头撞到了墙上，想了解如何做到这一点。这是在GAS中运行的脚本，用于写入MySQL表。

function LessCo1() {
var sheetName = 'Sheet';
var dbAddress = 'Address';
var dbUser = 'User';
var dbPassword = 'Pass';
var dbName = 'TestData';
var dbTableName = 'sampledata';
var dbURL = 'jdbc:mysql://' + dbAddress + '/' + dbName;
var sql = "INSERT INTO " + dbTableName + " (name, productkey, businesspartner, dateordered, price, quantity, promisedate, deliverydate, deliveredqty, market, doctype, docstat, docno, orderref) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

var maxRecordsPerBatch = 50;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);

var sheetData = sheet.getRange(1, 1, 10000, 14).getValues();
var dbConnection = Jdbc.getConnection(dbURL, dbUser, dbPassword);
var dbStatement = dbConnection.prepareCall(sql);

var name, productkey, businesspartner, dateordered, price, quantity, promisedate, deliverydate, deliveredqty, market, doctype, docstat, docno, orderref;
var recordCounter = 0;
var lastRow;
dbConnection.setAutoCommit(false);
for (var i = 1; i <= 9000; i++) 
{
lastRow = (i + 1 == sheetData.length ? true : false);

name = sheetData[i][0];
productkey = sheetData[i][1];
businesspartner = sheetData[i][2];
dateordered = sheetData[i][3];
price = sheetData[i][4];
quantity = sheetData[i][5];
promisedate = sheetData[i][6];
deliverydate = sheetData[i][7];
deliveredqty = sheetData[i][8];
market = sheetData[i][9];
doctype = sheetData[i][10];
docstat = sheetData[i][11];
docno = sheetData[i][12];
orderref = sheetData[i][13];


dbStatement.setString(1, name);
dbStatement.setString(2, productkey);
dbStatement.setString(3, businesspartner);
dbStatement.setString(4,dateordered);
dbStatement.setString(5, price);
dbStatement.setString(6, quantity);
dbStatement.setString(7, promisedate);
dbStatement.setString(8, deliverydate);
dbStatement.setString(9, deliveredqty);
dbStatement.setString(10, market);
dbStatement.setString(11, doctype);
dbStatement.setString(12, docstat);
dbStatement.setString(13, docno);
dbStatement.setString(14, orderref);
dbStatement.addBatch();
recordCounter += 1;
if (recordCounter == maxRecordsPerBatch || lastRow)
{
try {
dbStatement.executeBatch();
}
catch(e)
{
console.log('Attempted to update TABLE `' + dbTableName + '` in DB `' + dbName + '`, but the following error was returned: ' + e);
}
if (!lastRow)
{ 
dbStatement = dbConnection.prepareCall( sql );
recordCounter = 0;
}
}
}
dbConnection.commit();
dbConnection.close();
}

Answer 1

传递给表的日期格式不正确。您收到的错误告诉您字符串"2019年1月1日00:00:00 GMT-0500(东部标准时间(">不是dateordered列所需的datetime对象。

因此，您的日期需要转换为DATETIME - format: YYYY-MM-DD HH:MI:SS对象。要做到这一点，您需要使用字符串操作并删除不必要的部分。例如：

var myDate = 'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)'.split(" ").splice(0,6);
var timeZone = myDate.slice(-1)[0].split("GMT");
timeZone[0] = "GMT";
console.log(timeZone);
// ["GMT", "-0500"]
myDate = myDate.slice(0,5).join(" ");
console.log(myDate);
// "Tue Jan 01 2019 00:00:00"

既然您已经放弃了不必要的内容，让我们将该日期放入SQL表中，好吗？

下面是一个datetime类型的一列表的示例，您应该将其调整为var sql中的JS代码。

CREATE TABLE foo_bar (
dateordered datetime
);
INSERT INTO foo_bar (dateordered) VALUES (CONVERT_TZ(STR_TO_DATE(myDate,"%a %b %d %Y %T"),timeZone[0],timeZone[1]));
SELECT * FROM foo_bar ;
// Output: "2018-12-31 20:00:00" 

现在，您的DB表中有一个日期时间对象，该对象已转换为GMT时区，因此在读取数据库时请记住，在某些情况下，您可能需要使用CONVERT_TZ来本地化它。

使用STR_TO_DATE(str,format)将日期从字符串转换为SQL日期时间。

来自文档："；它采用字符串str和格式化字符串格式。如果格式字符串同时包含日期和时间部分，STR_TO_DATE((将返回DATETIME值；如果字符串仅包含日期或时间部分，则返回DATE或time值。如果从str中提取的日期、时间或日期时间值非法，str_TO_date((将返回NULL并产生警告">