CODEWITHSUNDEEP
google-bigquerysubstringbetween
BPM0612
BPM0612

Reputation: 1

No Matching Signature for Operator BETWEEN

Data describes Id, Activity Hour and StepTotal. ActivityHour is defined in the format YY-MM-DD HH:MM:SS.

I am looking to obtain entries between 2016-04-12 AND 2016-05-12 where the time is between 08:00:00 UTC AND 15:00:00 UTC and the StepTotal is less than 50. I want the result to be ordered by Id.

I thought I should use substring to separate date and time - but I am really going wrong somewhere.

Here's what I have:

SELECT Id, ActivityHour, StepTotal,
SUBSTR(ActivityHour, 11, 23) AS Time,
FROM `dataset` AS Time
WHERE ActivityHour BETWEEN '2016-04-12' AND '2016-05-12'
AND Time BETWEEN '08:00:00 UTC' AND '15:00:00 UTC' 
AND StepTotal < 50
ORDER BY Id, ActivityHour

Upvotes: 0

Views: 28

Answers (1)

camilajenny
camilajenny

Reputation: 5064

Are you labeling your table Time and then using it to query the actual time between your timepoints? Just because you call it time it doesn't make it time.

Maybe you can use extract function? Maybe try

SELECT Id, ActivityHour, StepTotal, SUBSTR(ActivityHour, 11, 23) AS Time, 
FROM dataset 
WHERE ActivityHour BETWEEN '2016-04-12' AND '2016-05-12' 
  AND EXTRACT(HOUR FROM ActivityHour) BETWEEN 8 AND 15
  AND StepTotal < 50 
ORDER BY Id, ActivityHour

Upvotes: 1

Related Questions