Rolling Window Expansion Prompt for Time-Series Retrieval

This prompt is designed for analytics RAG developers and search engineers who need to convert natural language rolling window expressions into precise, machine-readable date ranges. Queries like 'trailing 12 months', 'previous 7 days', or 'last 3 complete quarters' are common in business intelligence and time-series analysis but are useless for retrieval unless they resolve to explicit start and end dates. The core job-to-be-done is pre-retrieval normalization: taking a user query containing a relative time window and a reference date, then producing the exact date range boundaries, partition intervals if needed, and the inclusion rules applied so the downstream retriever can apply correct metadata filters.

Use this prompt as a pre-retrieval step in a RAG pipeline before querying a time-partitioned vector store, a SQL database, or a document index with timestamp metadata. The ideal user is a platform engineer or RAG developer who already has a working retrieval system and needs to handle the common failure mode where users express time constraints in business language that doesn't map directly to filter clauses. The prompt expects two required inputs: the user's natural language query containing a rolling window expression, and a reference date (typically the current date or the conversation's temporal anchor). It outputs structured date boundaries, not free-text explanations, so it can be consumed programmatically by filter generation code.

This prompt is not a general date parser and should not be used for fixed calendar dates ('January 15, 2024'), fiscal year resolution (which requires organization-specific calendars), holiday mapping (which varies by locale and year), or timezone conversion (which requires location context). For those use cases, pair this prompt with sibling playbooks like the Fiscal Quarter Resolution Prompt or the Holiday-Aware Date Range Expansion Prompt. The prompt also does not validate whether the computed range makes logical sense for the domain—if a user asks for 'trailing 100 years' against a corpus that only has 5 years of data, the prompt will faithfully compute the range, and it's the application's responsibility to detect and handle empty result sets. Always run the output through a temporal constraint validator before execution, and have a fallback relaxation strategy ready when strict windows return zero results.

This prompt template is designed to be pasted directly into your prompt layer. It takes a user query containing a rolling time window (e.g., 'trailing 12 months', 'previous 7 days') and a reference date, then outputs a precise date range with ISO 8601 boundaries. The template also generates any necessary partition boundaries to optimize retrieval against time-partitioned data stores. Replace every square-bracket placeholder with your application's specific values before sending the prompt to the model.

textCopy
You are a precise temporal query parser for a time-series retrieval system. Your job is to convert a user's query that specifies a rolling time window into an explicit, machine-readable date range and a list of partition boundaries.

INPUT QUERY: [USER_QUERY]
REFERENCE DATE (ISO 8601): [REFERENCE_DATE]
TARGET TIMEZONE: [TIMEZONE]

INSTRUCTIONS:
1. Identify the rolling window period specified in the query (e.g., 'trailing 12 months', 'last 7 days', 'previous quarter').
2. Calculate the exact start and end dates for this window relative to the REFERENCE DATE. The end date is inclusive and defaults to the REFERENCE_DATE unless the query specifies otherwise.
3. Determine the appropriate partition granularity for the window (e.g., daily, weekly, monthly, quarterly) based on the window size. Use this rule: windows <= 31 days get daily partitions; windows <= 12 months get monthly partitions; larger windows get quarterly partitions.
4. Generate a list of all partition boundaries within the calculated range. Each partition must be a contiguous, non-overlapping interval.
5. If the window is incomplete (e.g., the current month is not yet finished), flag it and note the incomplete partition.
6. Explicitly state the boundary inclusion rule: the start date is inclusive, the end date is inclusive.

OUTPUT_SCHEMA:
{
  "window_type": "string (e.g., 'trailing 12 months')",
  "start_date": "ISO 8601 date",
  "end_date": "ISO 8601 date",
  "inclusion_rule": "[inclusive, inclusive]",
  "partition_granularity": "string (daily | weekly | monthly | quarterly)",
  "partitions": [
    {
      "partition_start": "ISO 8601 date",
      "partition_end": "ISO 8601 date",
      "is_complete": boolean
    }
  ],
  "incomplete_partition_warning": "string | null"
}

CONSTRAINTS:
- All dates must be valid calendar dates in the TARGET TIMEZONE.
- Do not output dates beyond the REFERENCE_DATE unless the query explicitly asks for a future window.
- If the query's window is ambiguous (e.g., 'recent'), output a JSON object with an 'error' field explaining the ambiguity and suggesting a specific window.
- Do not include any text outside the JSON object.

EXAMPLES:
Query: 'trailing 3 months'
Reference Date: 2024-07-15
Output: {"window_type": "trailing 3 months", "start_date": "2024-04-01", "end_date": "2024-07-15", "inclusion_rule": "[inclusive, inclusive]", "partition_granularity": "monthly", "partitions": [{"partition_start": "2024-04-01", "partition_end": "2024-04-30", "is_complete": true}, {"partition_start": "2024-05-01", "partition_end": "2024-05-31", "is_complete": true}, {"partition_start": "2024-06-01", "partition_end": "2024-06-30", "is_complete": true}, {"partition_start": "2024-07-01", "partition_end": "2024-07-15", "is_complete": false}], "incomplete_partition_warning": "The final partition ending 2024-07-15 is incomplete."}

Query: 'previous 7 days'
Reference Date: 2024-07-15
Output: {"window_type": "previous 7 days", "start_date": "2024-07-08", "end_date": "2024-07-14", "inclusion_rule": "[inclusive, inclusive]", "partition_granularity": "daily", "partitions": [{"partition_start": "2024-07-08", "partition_end": "2024-07-08", "is_complete": true}, {"partition_start": "2024-07-09", "partition_end": "2024-07-09", "is_complete": true}, {"partition_start": "2024-07-10", "partition_end": "2024-07-10", "is_complete": true}, {"partition_start": "2024-07-11", "partition_end": "2024-07-11", "is_complete": true}, {"partition_start": "2024-07-12", "partition_end": "2024-07-12", "is_complete": true}, {"partition_start": "2024-07-13", "partition_end": "2024-07-13", "is_complete": true}, {"partition_start": "2024-07-14", "partition_end": "2024-07-14", "is_complete": true}], "incomplete_partition_warning": null}

To adapt this template, replace [USER_QUERY] with the raw user input, [REFERENCE_DATE] with the current date or a session-specific anchor in ISO 8601 format (e.g., 2024-07-15), and [TIMEZONE] with the IANA timezone string (e.g., America/New_York). The output JSON can be parsed directly by your retrieval harness to construct metadata filters or partition-scoped queries. For high-stakes financial or compliance use cases, always validate the generated dates against a deterministic calendar library before executing retrieval, and log the prompt output alongside the final query for auditability.

The rolling window expansion prompt is a pre-retrieval transformation step. It sits between the user's natural language query and the search index. The application receives a query like 'trailing 12 months of revenue,' calls the LLM with this prompt to resolve the relative window into explicit ISO 8601 date boundaries, and then injects those boundaries as metadata filter clauses into the vector or keyword search request. This prompt is not a conversational agent; it is a deterministic component in a retrieval pipeline. Treat it as a pure function: natural language temporal expression in, structured date range out.

Wire the prompt into your retrieval service as a synchronous pre-processing hook. The calling function should supply a [REFERENCE_DATE] (defaulting to the current UTC date if the user's session provides no anchor) and the [USER_QUERY]. Parse the model's output against a strict schema: expect a JSON object with start_date (ISO 8601 date string), end_date (ISO 8601 date string), window_label (the resolved human-readable label like '2024-03-15 to 2025-03-15'), and boundary_rule (either inclusive or exclusive for the end date). If the model returns malformed JSON, missing fields, or dates that fail Date.parse(), reject the output and retry once with a repair prompt that includes the raw output and the schema. After two failures, log the query and raw output, then fall back to a safe default window (e.g., the last 365 days) with an assumed_window: true flag so downstream answer generation can disclose the assumption to the user.

For production observability, log the input query, the resolved date range, the model used, and the latency of this expansion step. Set up an eval harness that runs a golden set of temporal expressions ('last 7 days', 'trailing 3 months', 'previous quarter') against known expected date ranges computed programmatically from the reference date. Run these evals on every prompt or model change. Common failure modes include: the model treating 'trailing 12 months' as exactly 365 days instead of the same calendar day one year prior; off-by-one errors on month boundaries; and misalignment when the reference date is near month-end. Your eval suite should explicitly test these edge cases. If the resolved window is used for financial or compliance queries, route the output through a human review queue before retrieval executes, or at minimum surface the resolved window to the user for confirmation.