Bigquery错误：400参数类型：DATE，TIMESTAMP，TIMESTAMP的运算符BETWEEN没有匹配的签名

Question

我已经在Google Cloud Bigquery上部署了webapp，当我查询数据时出现错误"400 No matching signature for operator BETWEEN for argument types: DATE, STRING, STRING. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [2:38]"。这是我的SQL：

"""SELECT 
   Record_Start_Time, Generator_Power 
FROM 
   Furnace.FurnaceData
WHERE 
   Record_Start_Time BETWEEN TIMESTAMP("2018-01-21")
AND 
  TIMESTAMP("2018-07-21") 
ORDER BY Record_Start_Time
LIMIT 100""".format(request.form['start'],request.form['end'])

Answer 1

根据您收到的错误消息（我同意您的问题中的注释，这很奇怪，我怀疑与该特定查询不符），它看起来像是字段Record_Start_Time类型DATE，而在BETWEEN操作符中您正在使用TIMESTAMP取而代之。

您应该了解收到的错误消息的方式如下：

[...] operator BETWEEN for argument types: DATE, STRING, STRING. Supported signature: (ANY) BETWEEN (ANY) AND (ANY)

此错误的装置，对于所支持的签名BETWEEN操作者field BETWEEN a AND b，其中field，a并且b应该是相同类型的（
ANY ）。此外，错误消息还告诉您您正在执行以下操作：_DATE_ BETWEEN _STRING_ AND _STRING_即，您正在尝试将一个DATE类型与一个STRING类型进行比较。这看起来很奇怪，因为TIMESTAMP("2018-01-21")它的TIMESTAMP类型不是STRING，但我想说的是，您过去曾尝试运行类似的查询WHERE Record_Start_Time BETWEEN "2018-01-21" AND "2018-07-21"，而您共享的错误消息就是与该查询相对应的错误消息。对于您共享的查询，错误消息应该是：

400 No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [2:38]

长话短说，确认Record_Start_Time字段的DATE类型和大小写，将您的WHERE子句更改为以下内容：

WHERE 
   Record_Start_Time BETWEEN DATE("2018-01-21")
AND 
  DATE("2018-07-21")