Bigquery错误:400参数类型:DATE，TIMESTAMP，TIMESTAMP的运算符BETWEEN没有匹配的签名 [英] Bigquery error: 400 No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP

问题描述

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

I have deployed my webapp on Google Cloud Bigquery, when I query the data I get an error "400 No matching signature for operator BETWEEN for argument types: DATE, STRING, STRING. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [2:38]". Here is my 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'])

推荐答案

根据您收到的错误消息(我同意您问题中的注释，这很奇怪，我怀疑与该特定查询不符) ，看起来字段Record_Start_Time的类型为DATE，而在BETWEEN运算符中，您使用的是TIMESTAMP值.

According to the error message you are getting (which, I agree with the comments in your question, is strange and I suspect does not correspond to this specific query), it looks like the field Record_Start_Time is of type DATE, while in the BETWEEN operator you are using TIMESTAMP values instead.

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

The way you should understand the error message you are getting, is the following:

[...] 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"的查询，而您共享的错误消息就是与此对应的错误消息.询问.对于您共享的查询，错误消息应该是:

This error means that the supported signature for the BETWEEN operator is field BETWEEN a AND b, where field, a and b should be of the same type (ANY). Additionally, the error message tells you that you are doing the following: _DATE_ BETWEEN _STRING_ AND _STRING_, i.e. you are trying to compare a DATE type with a STRING type. This looks strange because TIMESTAMP("2018-01-21") is of TIMESTAMP type and not STRING, but I would say that maybe you tried in the past running a query like WHERE Record_Start_Time BETWEEN "2018-01-21" AND "2018-07-21", and the error message you shared is the one corresponding to that query. For the query you shared, the error message should be:

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子句更改为以下内容:

---

Long story short, confirm that the Record_Start_Time field is of DATE type and being that the case, change your WHERE clause to the following:

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

这篇关于Bigquery错误:400参数类型:DATE，TIMESTAMP，TIMESTAMP的运算符BETWEEN没有匹配的签名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！