There is a data column in my dataset called "date". The values look like this:
"2022-07-23 04:16:51 UTC"
I am trying to select rows from my table like this:
SELECT
date
type,
mid,
wikipediaUrl,
numMentions,
avgSalience
FROM
myTable,
UNNEST(entities)
WHERE type = "LOCATION" AND score < -0.1 AND (date BETWEEN DATE(current_date(), INTERVAL 40 DAY) AND current_date())
However, I get an error on the between function:
No matching signature for function DATE for argument types: DATE, INTERVAL. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64); DATE(DATE); DATE(STRING) at [16:60]
What am I doing wrong?
CodePudding user response:
The function to specify the start date of your where filter should be date_sub(current_date(), interval 40 day)
CodePudding user response:
This value "2022-07-23 04:16:51 UTC"
is not a DATE
it is a TIMESTAMP
which is the issue you are seeing.
So in your where clause you should be doing something like this instead:
DATE(date) BETWEEN date_sub(current_date(), INTERVAL 40 DAY) and current_date
If I modify your query to be 60 days, so the timestamp provided fits in the between statement, like:
select sample_data.timestamp_value
,date(timestamp_value) date_value
from sample_data
where date(timestamp_value) between date_sub(current_date(), INTERVAL 60 DAY) and current_date
it returns the following