时间戳

首页 > 解决方案 > 从 Google Sheet 将可用日期写入 MySQL

mysql - 从 Google Sheet 将可用日期写入 MySQL

问题描述

我有一个脚本将数据从谷歌表写入谷歌云 MySQL 表，但希望有 Data Studio 可以使用的日期。我试过使用日期、日期时间和时间戳，但我得到了同样的错误：

尝试更新sampledataDB中的 TABLE TestData，但返回以下错误：异常：数据截断：日期时间值不正确：第 1 行的列 'dateordered' 的'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)'

我尝试使用ToDateData Studio 中的函数将其更改为日期，但仍然无法识别。我觉得我正在把头撞到墙上，并且想获得一些关于如何做到这一点的新想法。这是在 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();
}

标签： mysqlgoogle-apps-scriptjdbcgoogle-cloud-sqlgoogle-data-studio

解决方案

您传递给表格的日期格式不正确。您收到的错误告诉您字符串'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)'不是datetime它期望的dateordered列对象。

因此，您的日期需要转换为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.

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"

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

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

从文档中： “它需要一个字符串 str 和一个格式字符串格式。如果格式字符串包含日期和时间部分，则 STR_TO_DATE() 返回 DATETIME 值，如果字符串仅包含日期或时间部分，则返回 DATE 或 TIME 值。如果从 str 中提取的日期、时间或日期时间值非法，则 STR_TO_DATE() 返回 NULL 并产生警告。”

推荐阅读