sql - Bigquery 错误 : 400 No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP

Question

我已经在 Google Cloud Bigquery 上部署了我的网络应用程序，当我查询数据时出现错误 "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取而代之的值(value)观。

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

[...] 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")