CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Programming-Lover
Programming-Lover

Reputation: 1265

How to filter records between timestamps from information_schema.warehouse_load_history()

I want to filter the records between timestamps from information_schema.warehouse_load_history() somehow below query is returning the empty result.

Query

select date_part(epoch_millisecond, convert_timezone('UTC', END_TIME)), WAREHOUSE_NAME, AVG_RUNNING, AVG_QUEUED_LOAD, AVG_QUEUED_PROVISIONING, AVG_BLOCKED from table(information_schema.warehouse_load_history()) where date_part(epoch_millisecond, convert_timezone('UTC', END_TIME)) >= 1668081337659 and date_part(epoch_millisecond, convert_timezone('UTC', END_TIME)) <= 1668083015000

Upvotes: 1

Views: 143

Answers (2)

Gokhan Atil
Gokhan Atil

Reputation: 10059

The important point here is, the filters in the WHERE clause will be applied after the warehouse_load_history table function returns a result set. This rule is valid for any information schema table functions (ie query_history).

The function accepts DATE_RANGE_START, DATE_RANGE_END and WAREHOUSE_NAME parameters.

  • If an end date is not specified, then CURRENT_DATE is used as the end of the range.
  • If a start date is not specified, then the range starts 10 minutes prior to the start of DATE_RANGE_END

So your query only returns the last 10 minutes of data for all warehouses. Your WHERE filter is applied to this returning data.

In short, you should use the filters of the function first (as I said, it's the same for all information schema functions), and then you should use the WHERE clause for additional filters.

Upvotes: 2

patrick_at_snowflake
patrick_at_snowflake

Reputation: 453

You might be using that wrong, the dates are a part of the date function itself, no need to add a where clause outside of the table function itself!

For reference: https://docs.snowflake.com/en/sql-reference/functions/warehouse_metering_history.html

Code from ref: select * from table(information_schema.warehouse_metering_history('2017-10-23', '2017-10-23', 'testingwh'));

Upvotes: 0

Related Questions