No matching signature for function DATE for argument types-CodePudding

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