Date and Time Expression Normalization Prompt Template

This prompt is for RAG system operators and search engineers who need to convert relative, ambiguous, or colloquial time expressions in user queries into explicit, machine-readable date ranges for metadata filtering. The core job-to-be-done is preventing retrieval failures caused by queries like 'last quarter's reports' or 'next Monday's schedule' hitting a vector index that only understands ISO 8601 date constraints. The ideal user is a developer building a production retrieval pipeline over a time-sensitive corpus—such as financial filings, internal communications, or event logs—where a missed date boundary means retrieving irrelevant or empty results.

Use this prompt when your retrieval system supports structured metadata filters on date fields and your users express time constraints in natural language. It is designed to sit in the pre-retrieval query processing layer, after query validation but before the final search request is assembled. The prompt expects a user query containing a temporal expression, a reference timestamp (typically now in ISO 8601), and an optional timezone. It returns a normalized date range with an explicit start and end in ISO 8601, a confidence flag, and a rationale for boundary decisions. Do not use this prompt for queries that already contain explicit date ranges, for time-series forecasting tasks, or for generating relative time expressions from absolute dates. It is a normalization tool, not a reasoning engine for temporal logic.

Before integrating this prompt into a production pipeline, you must define your timezone handling strategy. Ambiguous expressions like 'end of day' or 'this weekend' resolve differently depending on whether you anchor to UTC, the user's local time, or the data corpus timezone. You should also prepare eval cases for boundary dates: queries issued at midnight, queries spanning daylight saving transitions, and queries with fiscal calendars that differ from calendar months. The prompt includes a confidence flag for a reason—wire it into your pipeline to trigger a human review or a clarification question when confidence is low. If your system cannot tolerate any incorrect date normalization, this prompt alone is insufficient; you will need a human-in-the-loop review step for low-confidence outputs.

This prompt template converts user-supplied relative time expressions—such as 'last quarter', 'next Monday', or 'the past few weeks'—into explicit, machine-readable ISO 8601 date ranges suitable for metadata filtering in RAG retrieval pipelines. It is designed to be dropped into a pre-retrieval normalization step where ambiguous temporal language would otherwise cause missed results or incorrect time scoping. The template expects a reference timestamp to anchor relative expressions, a target timezone for boundary calculation, and an optional list of business-specific fiscal or holiday calendars that override default calendar boundaries.

textCopy
You are a date and time normalization engine for a retrieval system. Your job is to convert relative or ambiguous date expressions into explicit ISO 8601 date ranges.

## INPUT
User query: [USER_QUERY]
Reference timestamp (ISO 8601): [REFERENCE_TIMESTAMP]
Target timezone (IANA format): [TARGET_TIMEZONE]
Fiscal calendar overrides (JSON, optional): [FISCAL_CALENDAR_OVERRIDES]

## OUTPUT SCHEMA
Return a JSON object with the following fields:
{
  "original_expression": "string (the extracted date expression from the query)",
  "start_date": "string (ISO 8601 date or datetime, inclusive start of range)",
  "end_date": "string (ISO 8601 date or datetime, inclusive end of range)",
  "confidence": "number (0.0 to 1.0, how certain the normalization is)",
  "ambiguity_flag": "boolean (true if the expression could map to multiple ranges)",
  "alternative_ranges": ["array of {start_date, end_date, interpretation} objects if ambiguous"],
  "reasoning": "string (brief explanation of how the range was derived)"
}

## CONSTRAINTS
- Use the reference timestamp as 'now' for all relative calculations.
- Apply the target timezone for day-boundary calculations. 'Today' starts at 00:00:00 in that timezone.
- If fiscal calendar overrides are provided, use them for 'quarter', 'fiscal year', and similar expressions. Otherwise use standard calendar quarters (Q1: Jan-Mar, Q2: Apr-Jun, Q3: Jul-Sep, Q4: Oct-Dec).
- For 'last X' expressions (last week, last month, last year), return the immediately preceding complete period, not the current partial period.
- For 'next X' expressions, return the immediately following complete period.
- For 'past N days/weeks/months', calculate the range from (reference - N units) to reference timestamp.
- If the expression is already an explicit date range, extract and normalize it without reinterpretation.
- If no date expression is found, return null for start_date and end_date with confidence 0.0.
- Flag ambiguous expressions (e.g., 'this Friday' when reference is Friday) and include alternative interpretations.

## EXAMPLES
Input: "sales report for last quarter" | Reference: 2025-07-15T10:00:00Z | Timezone: America/New_York
Output: {"original_expression": "last quarter", "start_date": "2025-04-01", "end_date": "2025-06-30", "confidence": 0.98, "ambiguity_flag": false, "alternative_ranges": [], "reasoning": "Standard calendar Q2 2025 is the quarter immediately preceding the reference date of July 2025."}

Input: "meetings from the past 2 weeks" | Reference: 2025-07-15T10:00:00Z | Timezone: America/Chicago
Output: {"original_expression": "past 2 weeks", "start_date": "2025-07-01", "end_date": "2025-07-15", "confidence": 0.95, "ambiguity_flag": false, "alternative_ranges": [], "reasoning": "14-day lookback from reference date, inclusive of both boundaries."}

Input: "data from this Friday" | Reference: 2025-07-18T09:00:00Z (Friday) | Timezone: Europe/London
Output: {"original_expression": "this Friday", "start_date": "2025-07-18", "end_date": "2025-07-18", "confidence": 0.70, "ambiguity_flag": true, "alternative_ranges": [{"start_date": "2025-07-25", "end_date": "2025-07-25", "interpretation": "next Friday"}], "reasoning": "Reference date is already Friday. 'This Friday' could mean today or next Friday. Flagged for disambiguation."}

## INSTRUCTIONS
1. Extract the date or time expression from the user query.
2. Resolve it against the reference timestamp and timezone.
3. Apply any fiscal calendar overrides if provided.
4. Produce the output JSON conforming to the schema above.
5. If the expression is ambiguous, set ambiguity_flag to true and include alternative interpretations.

To adapt this template for your own RAG pipeline, replace the square-bracket placeholders with values from your application context. The [USER_QUERY] should be the raw user input before any other preprocessing. The [REFERENCE_TIMESTAMP] should typically be the current server time in ISO 8601 format, though you may substitute a fixed reference for replay or testing. The [TARGET_TIMEZONE] must be an IANA timezone string such as America/Chicago or Asia/Tokyo—do not use UTC offsets alone, as they do not encode daylight saving rules. The optional [FISCAL_CALENDAR_OVERRIDES] field accepts a JSON object mapping fiscal period names to {start_month, start_day} objects, for example {"Q1": {"start_month": 2, "start_day": 1}} for a February-start fiscal year. If your application does not use fiscal calendars, omit this field or pass an empty object. Before deploying, validate the output against your downstream metadata filter schema to ensure the date formats match what your search index expects.

Integrating this prompt into a production system means treating it as a deterministic pre-retrieval filter, not a conversational assistant. The prompt expects a raw user query string and a reference timestamp (the user's 'now') as input. It must return a structured JSON object containing ISO 8601 date ranges, a resolved reference point, and a confidence flag. The application layer should never pass the raw user query directly to the retrieval engine if the normalization step succeeds and returns a high-confidence result. Instead, the application should use the resolved date ranges to construct explicit metadata filters (e.g., start_date >= [RESOLVED_START] AND end_date <= [RESOLVED_END]) that are appended to the retrieval query's filter clause. This keeps the semantic search vector or keyword query clean while offloading temporal precision to the database's indexed date fields.

The implementation harness must handle several failure modes gracefully. First, if the model returns a confidence score below your defined threshold (e.g., < 0.9), the system should either discard the temporal filter and retrieve broadly, or escalate to a human-in-the-loop review queue if the domain is high-stakes (e.g., financial audits, clinical records). Second, the application must validate the output schema strictly: check that resolved_start and resolved_end are valid ISO 8601 strings, that resolved_start is before resolved_end, and that the reference_timestamp matches the input. A retry loop with a maximum of two attempts can recover from malformed JSON, but persistent schema violations should trigger a fallback to a safe default (e.g., the last 90 days) and log the failure for prompt debugging. Third, timezone handling is a common source of silent errors. The harness should always pass an explicit UTC offset or IANA timezone string in the reference_timestamp field (e.g., 2025-03-15T10:30:00-04:00) and verify that the resolved output uses the same offset or converts it correctly.

For observability, log the original query, the resolved date range, the confidence score, and the model's raw output before any post-processing. This audit trail is essential for debugging retrieval failures where a user asks for 'last month's reports' but sees results from the wrong period. In high-compliance environments, this log becomes part of the retrieval decision record. When wiring this into a RAG orchestrator like LangChain or a custom FastAPI middleware, place the normalization step after PII redaction and spelling correction but before query embedding and vector search. Avoid the temptation to pass the normalized date expression back into the user-facing query string; keep it as a parallel structured filter to prevent confusing the semantic search with date literals. Finally, test the harness end-to-end with eval cases that include ambiguous boundaries ('end of last quarter' when the query arrives on the first day of the new quarter), relative expressions without a clear anchor ('recently'), and timezone edge cases to ensure the system degrades safely rather than silently retrieving the wrong data.