CODEWITHSUNDEEP
google-bigquery
flashsnake
flashsnake

Reputation: 562

Is there a easy way to get the data with timestamp == yesterday?

Is there a easy way to get the data with timestamp == yesterday?

I just want the data of yesterday.

SELECT
 COUNT(t0.user_id) AS t0_qt_AC5uO9oi
FROM
 `{something here}` AS t0
WHERE
 (t0.event_type = 'XXXXX')
ORDER BY
 t0_qt_AC5uO9oi ASC;

Not sure what is the best way.

Upvotes: 17

Views: 29269

Answers (2)

ThangLeQuoc
ThangLeQuoc

Reputation: 3108

Add-in to the answer of Mikhail, if (today) + (-1) day is not so natural to read, then let DATE_SUB solves it

SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS yesterday;

+---------------+
| yesterday     |
+---------------+
| 2022-06-29    |
+---------------+

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  CURRENT_DATE() today, 
  DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) yesterday

Assuming that you are asking for how to get yesterday's date!
Then of course you need to use it in your query respectively

for example 

WHERE t0.event_date = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)

Upvotes: 31

Related Questions