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

> Discover how Dify DB Query Plugin handles data type conversions for Timestamp, UUID, and float values. Learn about `DbUtil.run_query()` transformations for accurate data retrieval.

- Repository: [Junjie.M/dify-plugin-tools-dbquery](https://github.com/junjiem/dify-plugin-tools-dbquery)
- Tags: deep-dive
- Published: 2026-03-05

---

**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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/db_query/tools/db_util.py)** (and identically in **[`db_query_pre_auth/tools/db_util.py`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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:

```python

# 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:

```python
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:**

```json
[
  {
    "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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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`](https://github.com/junjiem/dify-plugin-tools-dbquery/blob/main/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.