institutional-trader/backend/database/TIME_FORMAT_FIX.md

Time Format Fix Guide

Problem: "34 AM" Issue

Some time entries in the database are stored with malformed formats:

• "34 AM" instead of "9:34 AM" or "11:34 AM" (missing hour)
• ":34 AM" instead of "9:34 AM" (missing hour, has colon)

This happens when the hour component is lost during data import or processing.

Root Cause

The time parsing logic in SQL queries expects formats like:

• "9:34 AM" (hour:minute AM/PM)
• "9:34:45 AM" (hour:minute:second AM/PM)

But some entries only have the minute component, making them unparseable.

Solution

Option 1: Quick Fix (Recommended)

Run the simple fix script that adds default hours:

-- Run this in your PostgreSQL database
\i backend/database/fix_time_format_simple.sql

This script:

• Fixes "34 AM" → "9:34 AM" (uses 9 AM as default for morning)
• Fixes "34 PM" → "1:34 PM" (uses 1 PM as default for afternoon)
• Fixes ":34 AM" → "9:34 AM"
• Fixes ":34 PM" → "1:34 PM"

Option 2: Comprehensive Fix

For a more sophisticated fix that tries to infer hours from context:

-- Run this in your PostgreSQL database
\i backend/database/fix_malformed_times.sql

This script:

• Looks at nearby records on the same date to infer the hour
• Falls back to defaults if no context is available
• Provides a preview before applying fixes

Option 3: Diagnostic Check

Before fixing, check how many entries are affected:

node backend/scripts/checkTimeFormat.js

This will show:

• How many malformed entries exist
• Examples of the malformed formats
• Percentage of affected data

Prevention

The import script has been updated (backend/scripts/importCSV.js) to automatically fix malformed times during import. The normalizeTime() function now:

1. Detects patterns like "34 AM" or ":34 AM"
2. Adds appropriate default hours
3. Formats the time correctly before insertion

Verification

After running the fix, verify with:

-- Check for remaining malformed entries
SELECT COUNT(*) 
FROM "OptionsFlow_monthly"
WHERE "CreatedTime" IS NOT NULL
  AND (
    ("CreatedTime"::text ~ '^\d{1,2}\s*(AM|PM)$' AND "CreatedTime"::text !~ '^\d{1,2}:\d{2}')
    OR "CreatedTime"::text ~ '^:\d{1,2}\s*(AM|PM)$'
  );

Should return 0 if all entries are fixed.

Notes

• The default hours (9 AM for morning, 1 PM for afternoon) are reasonable defaults for trading hours
• If you have better context about when these trades occurred, you can modify the fix scripts
• The SQL parsing logic in optionflowrockerscorer.sql already handles various formats, but it can't parse times without hours