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

> Master time dimension filters in natural language queries. Learn how Dat uses a human-in-the-loop workflow to clarify date ranges and generate precise SQL for your data.

- Repository: [Junjie.M/dat](https://github.com/junjiem/dat)
- Tags: how-to-guide
- Published: 2026-03-05

---

**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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/text_to_sql_user_prompt.txt) and [`text_to_sql_with_followup_user_prompt.txt`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/text_to_sql_rules.txt) file in [`dat-core/src/main/resources/prompts/default/text_to_sql_rules.txt`](https://github.com/junjiem/dat/blob/main/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:

```json
{
  "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:

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

```

### Generated SQL Output

Following the rules in [`text_to_sql_rules.txt`](https://github.com/junjiem/dat/blob/main/text_to_sql_rules.txt), the LLM produces range-based SQL with proper timestamp casting:

```sql
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:

```java
// 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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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.