How to Handle Time Dimension Filters in Natural Language Queries with Dat

Dat resolves ambiguous time references in natural language queries through a human-in-the-loop (HITL) workflow that prompts users for explicit date ranges, then injects the resolved time_range variable into follow-up LLM prompts to generate precise, range-based SQL.

The junjiem/dat open-source project provides an agentic text-to-SQL system that specifically addresses the complexity of time dimension filters in natural language queries. When users ask questions like "How many orders were placed last week?", the system leverages a structured rule set and interactive clarification flow to ensure accurate date-time filtering across different database schemas.

Core Components for Time Filter Processing

AskController and HITL Events

The AskController class in dat-servers/dat-server-openapi/src/main/java/ai/dat/server/openapi/controller/AskController.java defines the HITL_AI_REQUEST_EVENT constant and manages the Server-Sent Events (SSE) stream that facilitates human-in-the-loop interactions【L53-L77】. When the LLM encounters an ambiguous time reference that violates the strict date-range rules, this controller emits an event requesting the user to provide a specific time range for screening.

Text2SqlContentInjector and Prompt Variables

The Text2SqlContentInjector located at dat-agents/dat-agent-agentic/src/main/java/ai/dat/agent/agentic/Text2SqlContentInjector.java prepares the LLM prompt by injecting critical context variables including query_time (the current timestamp) and, during follow-up interactions, the user-provided time_range【L94-L103】. This component loads the static rule set from text_to_sql_rules.txt and merges it with dynamic conversation context to guide the LLM's SQL generation.

AgenticAskdataAgent Orchestration

The AgenticAskdataAgent class manages the end-to-end workflow, detecting when time dimension clarification is required and re-invoking the LLM chain after receiving the time_range input from the user. It coordinates between the initial question parsing and the follow-up SQL generation phases using the text_to_sql_user_prompt.txt and text_to_sql_with_followup_user_prompt.txt templates.

The Time Filter Workflow

The system processes time dimension filters through a specific multi-step sequence:

1. Initial Query Processing: The user submits a natural language question containing temporal references (e.g., "orders from yesterday").
2. Prompt Construction: Text2SqlContentInjector builds the initial prompt incorporating query_time and the rules from text_to_sql_rules.txt.
3. Ambiguity Detection: The LLM identifies that the time reference requires clarification and cannot satisfy the rule requiring specific date ranges.
4. HITL Request: The system emits a hitl_ai_request event via AskController with the message "Please provide the time range for screening"【L53-L77】.
5. User Clarification: The client application prompts the user for explicit dates (e.g., "2025-07-01 to 2025-07-07").
6. Follow-up Execution: The agent re-runs using the follow-up prompt template with the injected time_range variable.
7. SQL Generation: The LLM generates SQL adhering to the date-time rules, casting numeric timestamps and using range conditions.

Date-Time Rules and SQL Generation Standards

The text_to_sql_rules.txt file in dat-core/src/main/resources/prompts/default/text_to_sql_rules.txt enforces strict standards for time dimension handling【L17-L24】:

• Timestamp Casting: Numeric timestamp columns must be cast using helper functions like TO_TIMESTAMP_MILLIS or TO_TIMESTAMP_SECONDS.
• Range Expressions: Specific dates must always be expressed as ranges using >= and < operators rather than equality checks, ensuring inclusive start and exclusive end boundaries.
• Precision Handling: The rules account for different timestamp precisions (seconds vs. milliseconds) when generating comparison logic.

Implementation Examples

Receiving the HITL Request

When the system requires time range clarification, the SSE stream returns:

{
  "event": "hitl_ai_request",
  "data": {
    "conversation_id":"<id>",
    "timestamp":1756051200000,
    "ai_request":"Please provide the time range for screening",
    "wait_timeout":30
  }
}

Submitting the Time Range

The client responds with the resolved range through the follow-up endpoint:

{
  "conversation_id":"<id>",
  "tool_approval":"2025-07-01 to 2025-07-07"
}

Generated SQL Output

Following the rules in text_to_sql_rules.txt, the LLM produces range-based SQL with proper timestamp casting:

SELECT COUNT(*) AS order_cnt
FROM orders o
WHERE o.order_timestamp >= TO_TIMESTAMP_SECONDS('2025-07-01 00:00:00')
  AND o.order_timestamp < TO_TIMESTAMP_SECONDS('2025-07-08 00:00:00');

This example demonstrates the required timestamp casting and the exclusive end-date pattern mandated by the system rules.

Java Client Integration

Integrate the HITL flow using the Dat OpenAPI controller:

// Build initial request
AskRequest req = new AskRequest();
req.setConversationId(UUID.randomUUID().toString());
req.setAgentName("askdata");
req.setQuestion("How many orders were placed last week?");

// Open SSE stream
SseEmitter emitter = restTemplate.postForObject(
    url, req, SseEmitter.class);

// Handle time range requests
emitter.onEvent(event -> {
    if (event.getName().equals("hitl_ai_request")) {
        String range = askUserForRange(); // UI prompt
        AskUserResponse followUp = new AskUserResponse();
        followUp.setConversationId(
            event.getData().get("conversation_id"));
        followUp.setToolApproval(range);
        restTemplate.postForEntity(
            url + "/reply", followUp, Void.class);
    }
});

Summary

• Dat resolves ambiguous time filters through a structured HITL workflow defined in AskController.java【L53-L77】.
• The Text2SqlContentInjector manages prompt variables including query_time and time_range to provide temporal context【L94-L103】.
• Strict rules in text_to_sql_rules.txt enforce range-based filtering and proper timestamp casting for database compatibility【L17-L24】.
• The follow-up prompt template (text_to_sql_with_followup_user_prompt.txt) allows the LLM to incorporate user-provided date ranges into generated SQL.
• Range expressions use inclusive start (>=) and exclusive end (<) boundaries to accurately capture full days without missing boundary records.

Frequently Asked Questions

What happens when a user asks for "last week" without specifying exact dates?

The system emits a hitl_ai_request event through the AskController SSE stream, prompting the client to request specific start and end dates. The LLM cannot generate compliant SQL without explicit range boundaries, as the text_to_sql_rules.txt mandates specific date ranges rather than relative time expressions.

How does Dat prevent SQL injection when processing user-provided time ranges?

User-provided time ranges are injected into the prompt as the time_range variable within the follow-up template, not concatenated directly into SQL strings. The LLM parses this natural language range and generates parameterized SQL with proper timestamp casting functions like TO_TIMESTAMP_SECONDS(), ensuring database adapters receive structured date objects rather than raw user input.

Which timestamp formats does Dat support for time dimension filters?

According to the rules in text_to_sql_rules.txt, the system supports numeric timestamps requiring casting via TO_TIMESTAMP_MILLIS for millisecond precision or TO_TIMESTAMP_SECONDS for second precision. The generated SQL always converts these to explicit datetime strings in 'YYYY-MM-DD HH:MM:SS' format within range conditions.

Can the HITL workflow be automated to skip manual time range input?

While the current implementation in AgenticAskdataAgent requires explicit user confirmation for time ranges to ensure accuracy, the architecture supports extending the Text2SqlContentInjector to automatically resolve relative dates using the query_time variable. However, the source code emphasizes human verification for time dimensions to prevent off-by-one errors in business-critical queries.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}