Data Type Conversions in Dify DB Query Plugin: Handling Timestamp, UUID, and Float Values

The DbUtil.run_query() method in junjiem/dify-plugin-tools-dbquery converts pandas.Timestamp and datetime.date objects to formatted strings, serializes uuid.UUID values to strings, and coerces whole-number floats to integers before returning query results.

The junjiem/dify-plugin-tools-dbquery repository provides Dify plugins for executing SQL queries against various databases. When retrieving data type conversions from query results, the plugin must normalize complex Python objects into JSON-serializable primitives. This normalization occurs in the DbUtil class, ensuring that Timestamp, UUID, and float values are properly handled before being passed back to the Dify platform.

How DbUtil.run_query() Normalizes SQL Results

After Pandas loads the SQL result set into a DataFrame, the run_query() method defined in db_query/tools/db_util.py (and identically in db_query_pre_auth/tools/db_util.py) iterates over each record to perform type-specific conversions. This process guarantees that the final JSON payload contains only primitive types—strings, integers, floats, booleans, or null values.

Timestamp and Date Conversions

Datetime objects require explicit string formatting to ensure consistent JSON output. The implementation handles two specific cases:

• pandas.Timestamp values are converted using strftime('%Y-%m-%d %H:%M:%S'), producing strings formatted as YYYY-MM-DD HH:MM:SS
• datetime.date values are converted using strftime('%Y-%m-%d'), producing strings formatted as YYYY-MM-DD

These conversions occur at lines 89-92 in db_util.py, ensuring that database timestamp and date columns become human-readable strings in the final output.

UUID Serialization

Universally Unique Identifiers (UUIDs) returned by databases like PostgreSQL are handled at line 94 of db_util.py. The code checks for uuid.UUID instances and applies str(value) to convert the binary UUID object into its standard string representation (e.g., 550e8400-e29b-41d4-a716-446655440000).

Float-to-Integer Optimization

Floating-point numbers that represent whole numbers are automatically converted to integers to reduce payload size and improve readability. At lines 95-98 in db_util.py, the code checks if a float value has no fractional component using value.is_integer(). If true, the value is cast to int(value); otherwise, the original float is preserved.

Code Implementation Details

The conversion logic is implemented within the record processing loop of the run_query method:

# From db_query/tools/db_util.py (lines 89-98)

for record in data:
    for key, value in record.items():
        if isinstance(value, Timestamp):
            record[key] = value.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(value, date) and not isinstance(value, datetime):
            record[key] = value.strftime('%Y-%m-%d')
        elif isinstance(value, UUID):
            record[key] = str(value)
        elif isinstance(value, float):
            if value.is_integer():
                record[key] = int(value)

This implementation ensures that all complex database types are normalized before the records are returned to the Dify plugin interface.

Practical Usage Example

When querying a PostgreSQL database containing various data types, the automatic conversions ensure JSON-compatible output:

from db_query.tools.db_util import DbUtil

# Initialize database connection

db = DbUtil(
    db_type="postgresql",
    username="analytics_user",
    password="secure_pass",
    host="db.company.com",
    port="5432",
    database="production"
)

# Query containing TIMESTAMP, DATE, UUID, and FLOAT columns

sql = """
SELECT 
    event_timestamp,           -- TIMESTAMPTZ
    session_date,              -- DATE
    user_uuid,                 -- UUID
    satisfaction_score         -- FLOAT (e.g., 5.0)
FROM user_events
WHERE session_date >= '2024-01-01'
LIMIT 3;
"""

records = db.run_query(sql)
print(records)

Output result:

[
  {
    "event_timestamp": "2024-02-15 13:45:22",
    "session_date": "2024-02-15",
    "user_uuid": "550e8400-e29b-41d4-a716-446655440000",
    "satisfaction_score": 5
  }
]

Notice that the satisfaction_score value 5.0 has been converted to the integer 5, while the timestamp and UUID values are now JSON-safe strings.

Summary

• DbUtil.run_query() in db_query/tools/db_util.py performs mandatory data type conversions to ensure JSON serialization compatibility with the Dify platform.
• Timestamp values are formatted as YYYY-MM-DD HH:MM:SS strings, while date values use the YYYY-MM-DD format.
• UUID objects are converted to their standard string representations using str(value).
• Float values representing whole numbers are coerced to integers to optimize payload size and readability.
• These conversions apply consistently across both the standard and pre-authentication versions of the plugin (db_query and db_query_pre_auth).

Frequently Asked Questions

How does the plugin handle timezone-aware timestamps?

The plugin converts pandas.Timestamp objects to strings using the format '%Y-%m-%d %H:%M:%S'. If the database returns timezone-aware timestamps, the string representation will include the timezone offset if the underlying Timestamp object stores it, though the specific formatting depends on how Pandas interpreted the database's timestamp data.

Why are whole-number floats converted to integers?

The conversion logic at lines 95-98 of db_util.py checks if a float value has no fractional component using value.is_integer(). When this condition is met, the value is cast to an integer to reduce JSON payload size and improve readability (e.g., displaying 5 instead of 5.0 for a rating or count field).

Does the plugin support custom date or datetime formatting?

Currently, the date and timestamp formatting uses hardcoded strftime patterns ('%Y-%m-%d %H:%M:%S' for timestamps and '%Y-%m-%d' for dates) as implemented in db_query/tools/db_util.py. Users requiring different formats would need to modify the source code or perform additional string formatting on the returned results within their Dify workflow.

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