time-filtering

Name: time-filtering
Availability: InStock
Author: Treasure Data

Apply td_interval patterns to time columns for time-based SQL filtering.

21|24|Updated Oct 21, 2025

Search Tags:#sql #data analytics #td_interval_range #partition pruning #Treasure Data #time filtering #td_interval

Ready to run

npx skills add https://github.com/treasure-data/td-skills --skill time-filtering

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: time-filtering
Source: https://github.com/treasure-data/td-skills/tree/main/sql-skills/time-filtering
Command: npx skills add https://github.com/treasure-data/td-skills --skill time-filtering

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill simplifies complex time-based filtering by offering advanced td_interval patterns, offset dates, and debugging helpers to ensure accurate, fast queries over large datasets.

Core Features & Use Cases

Advanced td_interval patterns: Offset dates, relative ranges, and now-based ranges for precise time slicing.
td_interval_range debugging: Quick checks to validate start/end for time windows.
Partition pruning guidance: Best practices to optimize queries and prune partitions.
Use Case: A daily ETL job needs to fetch the last 7 days of events across multiple time zones; this Skill streamlines building the right interval expressions and validates them.

Quick Start

Use the time-filtering skill to generate a td_interval expression that selects the previous 7 days relative to your reference date and verify with td_interval_range.

Frequently Asked Questions about time-filtering

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I filter SQL queries by time range without slowing down large datasets?▼

Time filtering with td_interval patterns enables partition pruning, which skips irrelevant data blocks during query execution. By applying td_interval directly to your time column—using absolute, relative, or now-based references—you filter efficiently without transforming the column, keeping queries fast over terabytes of data.

What's the best way to query the last N days of data across multiple time zones?▼

Use td_interval expressions with relative ranges and time-zone-aware offsets to generate accurate start and end dates. The Skill handles timezone alignment and validates windows with td_interval_range, ensuring reproducible results regardless of where your data originates or when your ETL runs.

How do I verify that my time filtering window is correct before running a large query?▼

The td_interval_range debugging helper returns the actual start and end timestamps for your interval expression. Run it first to confirm your window matches expectations—particularly useful for month-to-date or year-to-date ranges—before executing expensive analytics queries.

Can I use td_interval for month-to-date and year-to-date reporting?▼

Yes. td_interval patterns support month-to-date and year-to-date ranges through relative offset syntax. The Skill provides templates for these common reporting windows and ensures alignment with td_scheduled_time so scheduled jobs produce consistent results across runs.

Why does my time filter not prune partitions even when I use a time column?▼

Partition pruning requires the time column to be used directly in the filter without transformation. Functions like date truncation or arithmetic on the column prevent pruning. The Skill guides you to structure filters that keep the column intact, maximizing partition elimination and query speed.

Do I need to handle time filtering differently for daily ETL jobs versus ad hoc queries?▼

Daily ETL jobs benefit from td_interval with relative ranges and td_scheduled_time alignment, ensuring consistent windows even as dates shift. Ad hoc queries can use absolute ranges. The Skill addresses both patterns and explains when to anchor to scheduled execution time versus query submission time.