google-bigquery bq

Google BigQuery timestamp comparison

there is a table "tbl1", having columns "name, surname, log_date". "log_date" is timestamp type.

How can I retrieve the records logged yesterday?

I am struggling with timestamp variable, I did something like:

declare yesterday timestamp
set (yesterday) = select TIMESTAMP_ADD(EXTRACT(Date FROM CURRENT_TIMESTAMP()), interval -1 day);

Above fails, so I am not able to use it later in my script:

SELECT distinct 
    name
FROM 
    `xxx.tbl1`
WHERE 
    log_date > yesterday

Also I tried:

SELECT 
    distinct name
FROM 
    `xxx.tbl1`
WHERE 
  log_date > TIMESTAMP_ADD(EXTRACT(Date FROM CURRENT_TIMESTAMP()), interval -1 day)

or

SELECT 
    distinct name
FROM 
    `xxx.tbl1`
WHERE 
  log_date > Select TIMESTAMP_ADD(EXTRACT(Date FROM CURRENT_TIMESTAMP()), interval -1 day)

or

WITH vars AS (
  SELECT EXTRACT(Select TIMESTAMP_ADD(EXTRACT(Date FROM CURRENT_TIMESTAMP()), interval -1 day) AS CustomDay
)
SELECT distinct 
    distinct name
FROM 
    `xxx.tbl1`, vars
WHERE 
    log_date > CustomDay

No luck.

Is there any other way, how to parse yesterday's date and use it in the select statement later on?

Solution

Without knowing the error you are getting, it seems that the problem is that you are trying to compare a timestamp (the log_date) with datetime (the extracted DATE). This should work:

SELECT 
    distinct name
FROM 
    `xxx.tbl1`
WHERE 
  DATE(log_date) = DATE_ADD(EXTRACT(DATE FROM CURRENT_TIMESTAMP()), INTERVAL -1 DAY)

Important: Use = for comparison to get the logs from yesterday only, > will get you the logs from today onwards.

One little thing: I would use DATE_SUB to subtract days instead of DATE_ADD with -1 (for readability), but kept it like that because of your example code.