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

Question

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

Answer 1

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.