DataFusion Extract records by hour from a Timestamp_micros field

Google Cloud Data Analytics
, ,
1

In Google Cloud DataFusion Studio, I have a BigQuery table as the source, and I am using the Wrangler where I am trying to filter the table results to only include records from the last hour. However, I haven’t found an option for this in JEXL directives. I would appreciate any guidance or help with this filtering.

2

Hi @gaguilerag ,

Welcome to Google Cloud Community!

While Wrangler and its JEXL expressions are excellent for quick transformations on individual data records, they aren’t designed for comprehensive time-based filtering across an entire dataset.

Here’s what you can do, you can filter records from the last hour by adding a filter transformation and using the following JEXL expression:

Timestamp_micros >= (CURRENT_TIMESTAMP() - 3600) * 1e6

This expression checks if the Timestamp_micros value is greater than or equal to the current timestamp minus one hour, effectively retaining only the records from the last hour. Once you apply this filter, it will display the relevant results.

For more information here are some reference for you:

I hope this helps.