Last N Days Expansion Prompt for Vector Search

This prompt is for search engineers and RAG developers who need to convert relative 'last N days' expressions from user queries into concrete, machine-readable date range boundaries and metadata filter clauses. The job-to-be-done is pre-retrieval query normalization: taking a natural language time constraint like 'last 30 days' and producing an explicit start date, end date, and the corresponding filter syntax for vector databases such as Pinecone, Weaviate, Qdrant, or Milvus. Without this step, a vector similarity search has no way to scope results to a rolling time window, and you end up retrieving semantically relevant but temporally irrelevant documents.

Use this prompt when your retrieval pipeline accepts user queries that contain relative day offsets and your document store has a timestamp metadata field that can be filtered. The ideal user is a platform engineer or search relevance developer wiring together a hybrid retrieval system where vector similarity and metadata filtering must combine. You need a reference date anchor (typically the current UTC timestamp at query time) and knowledge of your vector database's filter syntax. Do not use this prompt when the user query contains absolute date ranges already (e.g., 'from March 1 to March 15'), fiscal calendar references, or vague recency terms like 'recent' or 'latest'—those require different expansion prompts. This prompt also assumes a simple day-count offset; it does not handle business-day-only windows, holiday exclusions, or timezone-aware boundary alignment, which require additional normalization layers.

Before deploying, validate that your vector database supports the filter syntax this prompt generates. Test with rolling window correctness checks: run the prompt at known reference dates and verify the computed boundaries match expected date ranges, especially across month boundaries, year boundaries, and leap years. Wire the output into a pre-retrieval step that appends the generated filter to your vector search request. If the filter causes empty result sets, you may need a temporal relaxation fallback that broadens the window or removes the constraint. Always log the generated boundaries alongside the original query for observability and debugging.

This prompt template transforms relative time expressions like 'last 30 days' or 'past week' into explicit date ranges and metadata filter clauses suitable for vector databases. It accepts a user query containing a relative time reference, a reference date to anchor the calculation, and a target vector database dialect. The output includes ISO 8601 date boundaries, the generated filter syntax, and an explanation of the boundary logic so operators can verify correctness before the filter reaches production retrieval.

textCopy
You are a temporal query expansion engine for vector search systems. Your job is to convert relative time expressions in user queries into explicit date range boundaries and metadata filter clauses.

## INPUT
User Query: [USER_QUERY]
Reference Date (ISO 8601): [REFERENCE_DATE]
Target Vector DB Dialect: [DB_DIALECT]
Timezone: [TIMEZONE]

## TASK
1. Identify any relative time expression in the user query that refers to a rolling window of the last N days, weeks, months, or years.
2. Calculate the exact start and end dates for that window relative to the reference date.
3. Generate a metadata filter clause in the specified vector database dialect that enforces this date range.
4. If the query contains multiple temporal expressions, handle each one and note any conflicts.
5. If no temporal expression is found, return an empty filter and state that no temporal constraint was detected.

## OUTPUT SCHEMA
Return a JSON object with this structure:
{
  "detected_expression": "string | null",
  "window_size": {
    "value": number,
    "unit": "days" | "weeks" | "months" | "years"
  } | null,
  "date_range": {
    "start": "ISO 8601 date string",
    "end": "ISO 8601 date string",
    "inclusive_start": boolean,
    "inclusive_end": boolean
  } | null,
  "filter_clause": "string | null",
  "boundary_logic": "string explaining how boundaries were calculated",
  "warnings": ["string"]
}

## CONSTRAINTS
- The end date must be the reference date unless the query specifies otherwise.
- The start date is calculated as reference_date minus N days/weeks/months/years.
- For 'last N weeks', multiply N by 7 to get the day count.
- For 'last N months', subtract N months from the reference date month, handling year rollover correctly.
- For 'last N years', subtract N years from the reference date year.
- All dates must be in the specified timezone before conversion to ISO 8601.
- The filter clause must use the exact syntax of the specified vector database dialect.
- If the detected expression is ambiguous (e.g., 'last few days'), include a warning and use a reasonable default (7 days) while noting the assumption.

## SUPPORTED DB DIALECTS
- "pinecone": {"date_field": {"$gte": "start", "$lte": "end"}}
- "weaviate": {"operator": "And", "operands": [{"path": ["date_field"], "operator": "GreaterThanEqual", "valueDate": "start"}, {"path": ["date_field"], "operator": "LessThanEqual", "valueDate": "end"}]}
- "qdrant": {"must": [{"key": "date_field", "range": {"gte": "start", "lte": "end"}}]}
- "milvus": "date_field >= start && date_field <= end"
- "elasticsearch": {"range": {"date_field": {"gte": "start", "lte": "end"}}}
- "mongodb": {"date_field": {"$gte": ISODate("start"), "$lte": ISODate("end")}}

## EXAMPLES

Example 1:
User Query: "find documents from the last 30 days about vector search"
Reference Date: "2025-01-15"
DB Dialect: "pinecone"
Timezone: "UTC"
Output:
{
  "detected_expression": "last 30 days",
  "window_size": {"value": 30, "unit": "days"},
  "date_range": {
    "start": "2024-12-16",
    "end": "2025-01-15",
    "inclusive_start": true,
    "inclusive_end": true
  },
  "filter_clause": "{\"date_field\": {\"$gte\": \"2024-12-16\", \"$lte\": \"2025-01-15\"}}",
  "boundary_logic": "Subtracted 30 days from reference date 2025-01-15 to get start date 2024-12-16. End date is the reference date. Both boundaries are inclusive.",
  "warnings": []
}

Example 2:
User Query: "show me recent papers on attention mechanisms"
Reference Date: "2025-01-15"
DB Dialect: "weaviate"
Timezone: "America/New_York"
Output:
{
  "detected_expression": "recent",
  "window_size": {"value": 90, "unit": "days"},
  "date_range": {
    "start": "2024-10-17",
    "end": "2025-01-15",
    "inclusive_start": true,
    "inclusive_end": true
  },
  "filter_clause": "{\"operator\": \"And\", \"operands\": [{\"path\": [\"date_field\"], \"operator\": \"GreaterThanEqual\", \"valueDate\": \"2024-10-17\"}, {\"path\": [\"date_field\"], \"operator\": \"LessThanEqual\", \"valueDate\": \"2025-01-15\"}]}",
  "boundary_logic": "Detected vague recency term 'recent'. Defaulted to 90-day window. Subtracted 90 days from reference date in America/New_York timezone to get start date 2024-10-17.",
  "warnings": ["Vague recency term 'recent' defaulted to 90 days. Adjust window_size if a different recency window is appropriate for this domain."]
}

## RISK_LEVEL
[RISK_LEVEL]

If RISK_LEVEL is "high", include an additional field "human_review_required": true and do not return a filter_clause until the date range has been reviewed.

Adapt this template by replacing the [USER_QUERY], [REFERENCE_DATE], [DB_DIALECT], [TIMEZONE], and [RISK_LEVEL] placeholders with runtime values from your application. The reference date should typically be the current date or the date of the user's session, injected by your application layer rather than extracted from the query itself. For production deployments, add your vector database's actual metadata field name in place of the date_field placeholder used in the dialect examples. If your system uses a database dialect not listed, extend the SUPPORTED DB DIALECTS section with the correct filter syntax before deploying. The RISK_LEVEL placeholder should be set to "high" when the query is used in regulated domains such as financial reporting, healthcare, or legal discovery, which will gate the output behind a human review flag.

The Last N Days Expansion prompt is designed to sit between the user's natural language query and your vector database's filter clause. In a typical RAG pipeline, you'll call this prompt before executing the retrieval step. The application should extract the user's original query, pass it to the LLM with this prompt template, parse the structured output, and then inject the resulting date range into your vector search's metadata filter. This prompt is not a retrieval step itself—it's a query pre-processing step that must complete successfully before any search is executed.

For implementation, wrap the prompt call in a function that accepts the user query and a reference date (defaulting to the current UTC timestamp). The function should: (1) format the prompt with the query and reference date, (2) call a fast, low-cost model like GPT-4o-mini or Claude Haiku, (3) parse the JSON response and validate that start_date and end_date are valid ISO 8601 strings where start_date is before end_date, and (4) return the date range or raise a structured error. If the model returns malformed JSON, implement a single retry with a repair-focused follow-up prompt that includes the raw output and a request for valid JSON. After two failures, fall back to a safe default (e.g., no date filter) and log the incident for review. Do not silently drop the filter—missing temporal constraints can flood the user with irrelevant results.

When integrating with vector databases like Pinecone, Weaviate, or Qdrant, map the validated start_date and end_date to your collection's date metadata field using the database's native filter syntax. For example, in Pinecone's metadata filter: {"date": {"$gte": "2024-01-01T00:00:00Z", "$lte": "2024-01-31T23:59:59Z"}}. Always use inclusive boundaries on both ends unless your use case demands exclusive semantics. Add observability by logging the original query, the expanded date range, the model used, latency, and any validation failures. This trace data is essential for debugging retrieval quality issues and tuning the prompt over time. Before deploying, run the prompt against a golden test set of queries with known date ranges to catch regressions in boundary calculation or format compliance.