Temporal Data and Timestamp Column Generation Prompt Template

This playbook is built for backend developers and data engineers who must guarantee that every generated timestamp includes an explicit timezone offset, survives automated validation, and inserts cleanly into TIMESTAMPTZ, DATETIME, or equivalent columns. The prompt handles relative expressions ('next Tuesday', 'end of Q3'), absolute dates, epoch conversions, and date range generation. It is not a replacement for your application's date library; it is the normalization layer between human language and machine-readable temporal data.

The ideal user is an engineer building a data ingestion pipeline, an AI-assisted data entry form, or a migration script where human-provided temporal descriptions must become strict database values. Required context includes the target column type, the expected output format (ISO 8601, epoch, or a specific database literal), and the relevant timezone if the input is ambiguous. Do not use this prompt when you need to perform date arithmetic, schedule recurring events, or handle calendar-specific business logic—those operations belong in application code after the timestamp is generated.

Before wiring this prompt into a production pipeline, define a validation layer that rejects outputs missing a timezone offset, parses the result with your database driver to confirm insertability, and logs any normalization failures for review. For high-risk domains such as financial transactions or compliance audit trails, require human approval on outputs where the model's confidence is low or the input description is ambiguous. Start by copying the template, replacing the placeholders with your schema and constraints, and running it against a golden set of temporal expressions to establish a baseline before deployment.

This prompt template enforces strict temporal discipline for database record generation. It requires the model to produce timestamps with explicit UTC offsets, resolve all relative expressions against a provided reference point, and reject any input that cannot be mapped to an unambiguous instant. The output is structured for direct validation against your database schema before insertion. Use this when generating audit logs, time-series data, event records, or any row where incorrect timezone handling would corrupt downstream queries.

textCopy
You are a temporal data generator. Your task is to produce timestamp column values for database INSERT statements.

## INPUT
[INPUT_DESCRIPTION]

## TARGET SCHEMA
[TABLE_NAME]
Columns requiring timestamps:
[TIMESTAMP_COLUMNS_WITH_TYPES]

## REFERENCE POINT
Current reference timestamp: [REFERENCE_TIMESTAMP] (format: ISO 8601 with offset, e.g., 2025-01-15T14:30:00-05:00)
Default timezone when none specified: [DEFAULT_TIMEZONE_OFFSET]

## OUTPUT FORMAT
Return a JSON object with this exact structure:
{
  "records": [
    {
      "[COLUMN_NAME]": "ISO 8601 string with explicit offset",
      ...
    }
  ],
  "resolution_notes": ["per-record explanation of how each timestamp was derived"]
}

## RULES
1. Every timestamp MUST include an explicit UTC offset (e.g., -05:00, +01:00, Z). Never output timestamps without an offset.
2. Resolve all relative expressions ("yesterday", "next week", "end of month") against the reference timestamp above. If a relative expression cannot be resolved unambiguously, flag it in resolution_notes and use the most reasonable interpretation.
3. If the input contains "now", "today", "current", or similar, use the reference timestamp, not the model's training cutoff.
4. For date-only inputs (no time component), default to [DEFAULT_TIME_OF_DAY] in the specified timezone.
5. If the input specifies a named timezone (e.g., "America/New_York"), convert to the correct UTC offset for the given date, accounting for DST if applicable.
6. If the input is ambiguous (e.g., "3/4/2025" could be March 4 or April 3), use [DATE_FORMAT_PREFERENCE] and note the resolution in resolution_notes.
7. Reject inputs that cannot be mapped to a valid timestamp. For rejected inputs, return null for that timestamp and explain why in resolution_notes.
8. Validate that all output timestamps are within the acceptable range: [MIN_TIMESTAMP] to [MAX_TIMESTAMP].

## CONSTRAINTS
[ADDITIONAL_CONSTRAINTS]

## EXAMPLES
Input: "created on Jan 15 2025 at 2:30 PM Eastern"
Reference: 2025-01-20T12:00:00-05:00
Output:
{
  "records": [
    {
      "created_at": "2025-01-15T14:30:00-05:00"
    }
  ],
  "resolution_notes": ["Eastern timezone mapped to -05:00 for January (EST). No DST adjustment needed."]
}

Input: "updated yesterday at noon"
Reference: 2025-01-20T12:00:00-05:00
Output:
{
  "records": [
    {
      "updated_at": "2025-01-19T12:00:00-05:00"
    }
  ],
  "resolution_notes": ["'yesterday' resolved relative to reference timestamp 2025-01-20. 'noon' interpreted as 12:00:00."]
}

Input: "scheduled for next Tuesday"
Reference: 2025-01-20T12:00:00-05:00 (Monday)
Output:
{
  "records": [
    {
      "scheduled_at": "2025-01-28T[DEFAULT_TIME_OF_DAY]-05:00"
    }
  ],
  "resolution_notes": ["'next Tuesday' from Monday Jan 20 resolves to Jan 28. Time defaulted to [DEFAULT_TIME_OF_DAY]."]
}

## OUTPUT
Return ONLY the JSON object. No markdown fences, no commentary.

Replace every square-bracket placeholder before use. [INPUT_DESCRIPTION] should describe what the user or upstream system provides (e.g., "natural language description of event times" or "array of date strings from CSV column"). [TIMESTAMP_COLUMNS_WITH_TYPES] must list each timestamp column with its SQL type (TIMESTAMPTZ, TIMESTAMP, DATE) so the model knows the required precision. [REFERENCE_TIMESTAMP] is critical: set it to the current time when the prompt runs, not a static value. [DEFAULT_TIMEZONE_OFFSET] should match your application's convention (e.g., -05:00 for US Eastern, +00:00 for UTC). [DEFAULT_TIME_OF_DAY] handles date-only inputs (commonly "00:00:00" for start-of-day or "23:59:59" for end-of-day). [DATE_FORMAT_PREFERENCE] resolves ambiguous numeric dates ("MM/DD/YYYY" or "DD/MM/YYYY"). [MIN_TIMESTAMP] and [MAX_TIMESTAMP] define your valid range; set these to catch wildly incorrect values before they hit your database. [ADDITIONAL_CONSTRAINTS] can include business rules like "timestamps must be in the past" or "scheduled_at must be after created_at."

After copying this template, wire the output through a schema validator that checks ISO 8601 compliance, offset presence, and range constraints before any INSERT executes. The resolution_notes field is your audit trail: log it alongside each record so operators can trace how every timestamp was derived. If your database uses TIMESTAMPTZ, the explicit offsets will be normalized to UTC on insert; the prompt's offset requirement ensures the model makes a conscious timezone decision rather than leaving it implicit. For high-stakes pipelines (compliance, billing, audit logs), add a human review step for any record where resolution_notes contains a warning or where the validator flags an edge case.

This prompt is designed to be called from an application service, not used in a chat interface. The primary integration pattern is a structured output API call where the prompt is the system message, the table schema and generation rules are provided in [CONTEXT], and the model is instructed to return a JSON array of records matching [OUTPUT_SCHEMA]. Because temporal data is highly sensitive to timezone errors and DST edge cases, the application layer must perform strict post-generation validation before any row touches a database.

Validation and Retry Loop: After the model returns the JSON payload, run a multi-step validator. First, confirm every timestamp field parses to a valid ISO 8601 string with an explicit offset (reject bare UTC Z if the target column requires a specific zone like America/Chicago). Second, for any relative expression like next Tuesday at 10:00 AM, compute the expected absolute timestamp server-side and compare it to the model's output within a 60-second tolerance. Third, validate that date ranges have start < end and that intervals do not overlap for the same entity. If validation fails, construct a retry prompt that includes the original input, the failed output, and a specific error message (e.g., Row 3: end_date is before start_date). Limit retries to 2 attempts, then route to a dead-letter queue for human review.

Observability and Logging: Log every generation request with a trace_id, the model version, the prompt template hash, and the input parameters. On success, log the row count and a hash of the output array. On validation failure, log the specific validator that failed, the offending row index, and the raw model output. This data is critical for detecting drift in model behavior around DST transitions or ambiguous relative expressions. For high-throughput pipelines, sample 5% of successful generations and run an additional eval that checks whether generated timestamps fall within expected business hours or plausible temporal ranges for the domain.

Model Choice and Tool Use: This prompt works best with models that support strict structured output modes (e.g., GPT-4o with response_format set to json_schema, or Claude 3.5 Sonnet with tool use for record generation). Do not use this prompt with models that lack strong instruction-following for complex schemas, as timestamp format drift is a common failure mode. If the generation requires resolving timezone abbreviations (e.g., EST vs EDT), provide a lookup table in [CONTEXT] rather than relying on the model's internal knowledge, which is often outdated or ambiguous. For production systems, consider wrapping the prompt call in a function tool definition where the tool's parameters enforce the exact timestamp format, making validation simpler and reducing retry rates.

When to Escalate: If the model consistently fails on DST boundary dates (e.g., March 10 or November 3), do not add more prompt instructions. Instead, move the ambiguous date resolution into application code that uses a timezone library like pytz or zoneinfo. The prompt should handle the common case; the application must own the edge cases. For regulated environments where timestamp accuracy has compliance implications, always require human approval on the first 100 generations after deployment and on any row where the model's confidence score (if available) falls below a configurable threshold.