CODEWITHSUNDEEP
sqlgoogle-bigquery
Vishu
Vishu

Reputation: 35

How to fetch data from events_ table in bigquery for last 24 hours by using standardsql?

I m using this query and i want to fetch the data of last 24 hours from the events_ table...

 Select           
  CAST(TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 330 
   MINUTE) AS date) AS event_date,
 event_name,user.value.string_value as context_device_id, 
 (event.value.string_value) as id,
 (event_param.value.string_value) as contentType
            FROM ``,
       UNNEST(user_properties) AS user, 
       UNNEST(event_params) as event,
       UNNEST(event_params) as event_param
   where user.key="email" and event.key="postID" and 
   event_param.key="article_type" and
  CAST(TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 330 
MINUTE)AS date) between DATE_SUB(current_date(), INTERVAL 1 DAY) and 
 DATE_SUB(current_date(),INTERVAL 0 DAY)

But I want whenever query will run gives the data of last 24 hours only means if i running the query at event 5pm today then it should fetch the data from yesterday 5pm to today's 5pm?

Upvotes: 1

Views: 3495

Answers (3)

Elliott Brossard
Elliott Brossard

Reputation: 33765

You need to update your table reference to use a wildcard, which can include multiple days, and then add a filter to restrict the tables that it matches. For example, you would want something like:

...
FROM `events_*`,
   UNNEST(user_properties) AS user, 
   UNNEST(event_params) as event,
   UNNEST(event_params) as event_param
WHERE _TABLE_SUFFIX >=
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND
  user.key="email" and
  ...

The filter on the _TABLE_SUFFIX pseudo-column restricts the scan to the tables for today and yesterday, and then the filter on the timestamp as in your original query further restricts to a 24 hour span.

Upvotes: 2

SQL_M
SQL_M

Reputation: 2475

As an alternative, GETDATE() in MSSQL will let you do integer division:

SELECT GETDATE(), GETDATE() - 1

Result at this moment: 2018-08-31 07:38:18.260 2018-08-30 07:38:18.260

So in your case BETWEEN GETDATE() - 1 AND GETDATE() will do the trick as well.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

In case sql server you can use this in where clause

where event_timestamp>=dateadd (hour , -24 , getdate()) and event_timestamp<getdate()

Upvotes: 0

Related Questions