ReddRadar
Agent skill
time-filtering
Advanced td_interval patterns including offset dates (-1d/2025-10-01, -7d/-1d, 0M/now), td_interval_range for debugging, td_time_string for display formatting, and partition pruning optimization.
Install this agent skill to your Project
npx add-skill https://github.com/treasure-data/td-skills/tree/main/sql-skills/time-filtering
SKILL.md
TD Time Filtering - Advanced Patterns
td_interval Offset Date Syntax
Reference date can be absolute, relative, or now:
-- Absolute reference date
where td_interval(time, '-1d/2025-10-01') -- Yesterday relative to Oct 1
where td_interval(time, '-7d/2025-10-01', 'JST')
-- Relative reference date
where td_interval(time, '-1d/-7d') -- Yesterday relative to 7 days ago
where td_interval(time, '-7d/-1d') -- Last 7 days from yesterday
-- now keyword
where td_interval(time, '-1d/now') -- Yesterday until now
where td_interval(time, '0d/now') -- Beginning of today until now
where td_interval(time, '0M/now') -- Beginning of this month until now
where td_interval(time, '0y/now') -- Beginning of this year until now
-- Combined patterns
where td_interval(time, '-7d/0M') -- Last 7 days from month start
where td_interval(time, '0M/-7d') -- This month up to 7 days ago
Interval Units
s=seconds, m=minutes, h=hours, d=days, w=weeks, M=months, q=quarters, y=years
td_interval_range for Debugging
select td_interval_range('-7d', 'UTC') -- Returns [start, end] timestamps
select td_interval_range('-1d/2025-10-01', 'JST') -- Check offset date range
select td_interval_range('0M/now', 'JST') -- Verify month-to-date range
td_time_string for Display
select td_time_string(time, 'd!') -- 2025-01-15 (UTC)
select td_time_string(time, 'd!', 'JST') -- 2025-01-15 (JST)
select td_time_string(time, 'M!', 'JST') -- 2025-01
select td_time_string(time, 'h!', 'JST') -- 2025-01-15 10
Codes: y! q! M! w! d! h! m! s!. Timezone optional (defaults to UTC). Use for display/grouping only, not filtering.
td_scheduled_time Reference
In workflows, include td_scheduled_time() to establish reference time:
select td_scheduled_time(), *
from mydb.events
where td_interval(time, '-1d', 'JST')
Partition Pruning Gotchas
-- BAD: Functions on time prevent pruning
where date(from_unixtime(time)) = '2025-01-15'
-- GOOD: Use td_time_range
where td_time_range(time, '2025-01-15', '2025-01-16')
-- BAD: Raw timestamp comparison
where time > 1704067200
-- GOOD: Use td_interval
where td_interval(time, '-7d')
-- BAD: Different column
where created_at > current_date - interval '7' day
-- GOOD: Use time column
where td_interval(time, '-7d')
Troubleshooting Empty Results
-- Check data range
select min(time), max(time) from mydb.events
-- Try different timezones
select count(*) from mydb.events where td_interval(time, '-1d') -- UTC
select count(*) from mydb.events where td_interval(time, '-1d', 'JST') -- JST
Resources
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
treasure-data/td-skills
email-campaign
This skill should be used when the user asks to "create an email", "build an email campaign", "design an email template", "generate an email for a segment", "preview an email", or "push an email to Engage". Generates enterprise-grade HTML email templates with live preview in Treasure Studio and natural language editing, then pushes the final version to Treasure Engage.
treasure-data/td-skills
action-report
YAML format reference for action reports rendered via preview_action_report. MUST be read before writing any action report YAML — defines the report structure (title, summary, actions array) and action item fields (as_is, to_be, reason, priority, category, impact) with incremental build workflow. Required by seo-analysis and any skill that produces prioritized recommendations.
treasure-data/td-skills
grid-dashboard
YAML format reference for grid dashboards rendered via preview_grid_dashboard. MUST be read before writing any dashboard YAML — defines the page structure, 6 cell types (kpi, gauge, scores, table, chart, markdown), grid layout rules, cell merging syntax, and incremental build workflow. Required by seo-analysis and any skill that produces visual data dashboards.
treasure-data/td-skills
seo-analysis
Runs SEO and AEO (Answer Engine Optimization) analysis on websites or specific pages. Use when the user mentions SEO, AEO, search rankings, search optimization, or wants to analyze how their pages perform in search engines and AI answers. Produces a data dashboard and action report with before/after recommendations.
treasure-data/td-skills
aps-doc-core
Core documentation generation patterns and framework for Treasure Data pipeline layers. Provides shared templates, quality validation, testing framework, and Confluence integration used by all layer-specific documentation skills.
treasure-data/td-skills
aps-doc-id-unification
Expert documentation generation for ID unification layers. Documents identity resolution algorithms, merge strategies, match rules, entity graphs, and multi-workflow orchestration. Use when documenting ID unification processes.
Didn't find tool you were looking for?