CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Spark2.0
Spark2.0

Reputation: 101

Bigquery error: 400 No matching signature for operator BETWEEN for argument types: DATE, TIMESTAMP, TIMESTAMP

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'])

Upvotes: 9

Views: 83299

Answers (2)

Giselle Falcao
Giselle Falcao

Reputation: 1

where ((data_resultado between '2023-10-20' AND '2023-10-30') OR (data_resultado between'2023-10-25' AND '2023-11-05'))

Upvotes: 0

dsesto
dsesto

Reputation: 8178

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)

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]

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")

Upvotes: 13

Related Questions