How to run BigQuery for Selection of last month Records?

0
votes

I am trying to get records from BigQuery Table for last month. I found that my column is in TimeStamp format, that's why it is giving an error. No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [4:21]

Table Structure enter image description here

Query

SELECT user_mobile,count(*) as total_customer FROM `Project.Dataset.Table` cr
where cr.DATE BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
group by user_mobile
having count(*) >=1;

Please guide how can I use timestamps in my query to get my required results. Thank you.

1
google-bigquery

1 Answers

2
votes

Use CURRENT_TIMESTAMP() and TIMESTAMP_SUB() instead:

SELECT user_mobile,count(*) as total_customer 
FROM `Project.Dataset.Table` cr
where cr.created_at BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP()
group by user_mobile
having count(*) >=1;

or convert created_at to date with DATE(cr.created_at)