institutional-trader/backend/database/README_DATE_FIX.md

Database Date/Time Fix Script

This script fixes date and time format issues in the OptionsFlow_monthly and options_flow tables.

What It Fixes

1. CreatedDate - Normalizes all dates to YYYY-MM-DD format

   • Converts MM/DD/YYYY → YYYY-MM-DD
   • Converts M/D/YYYY → YYYY-MM-DD
   • Keeps already valid YYYY-MM-DD dates

2. CreatedTime - Normalizes all times to HH:MM:SS AM/PM format with consistent spacing

   • Adds space before AM/PM: "3:59:59PM" → "3:59:59 PM"
   • Fixes missing seconds: "3:59 PM" → "3:59:00 PM"
   • Fixes missing colon: "359PM" → "3:59:00 PM"
   • Fixes missing hour: ":59 AM" → "9:59:00 AM"

3. ExpirationDate - Normalizes to YYYY-MM-DD format

   • Same normalization as CreatedDate

4. options_flow table - Fixes invalid timestamps

   • Sets NULL for dates outside valid range
   • Updates invalid created_at to current time

Usage

Option 1: Run SQL File Directly

# Connect to your PostgreSQL database
psql -h localhost -U postgres -d institutional_trader

# Run the fix script
\i backend/database/fix_all_dates_and_times.sql

Option 2: Use Node.js Script

# Analyze current state (no changes)
node backend/scripts/fixDatabaseDates.js analyze

# Run fixes
node backend/scripts/fixDatabaseDates.js fix

# Verify fixes
node backend/scripts/fixDatabaseDates.js verify

# Run all (analyze, fix, verify)
node backend/scripts/fixDatabaseDates.js all

Option 3: Use in Supabase SQL Editor

1. Open Supabase Dashboard → SQL Editor
2. Copy contents of fix_all_dates_and_times.sql
3. Paste and run in SQL Editor

What to Expect

Before Fix:

CreatedDate: "12/17/2025", "2025-12-17", "12-17-2025"
CreatedTime: "3:59:59PM", "359PM", "3:59 PM", ":59 AM"

After Fix:

CreatedDate: "2025-12-17" (all normalized)
CreatedTime: "3:59:59 PM" (all with space, consistent format)

Safety

• Read-only analysis first: Run analyze command to see what will be changed
• Non-destructive: Script only updates format, doesn't delete data
• Idempotent: Safe to run multiple times (won't change already-correct data)
• Verification: Includes verification queries to check results

Database Requirements

• PostgreSQL 12+ recommended
• Connection to OptionsFlow_monthly table
• Sufficient permissions for UPDATE operations

Troubleshooting

Permission Errors

-- Grant necessary permissions
GRANT UPDATE ON "OptionsFlow_monthly" TO your_user;

Timeout Errors

-- For large tables, increase timeout
SET statement_timeout = '10min';

Check Specific Records

-- See problematic entries
SELECT "CreatedDate", "CreatedTime", "Symbol"
FROM "OptionsFlow_monthly"
WHERE "CreatedTime"::text !~ '^\d{1,2}:\d{2}(:\d{2})?\s+[AP]M$'
  AND "CreatedTime" IS NOT NULL
LIMIT 100;

Notes

• Script preserves NULL values (doesn't change them)
• Ambiguous formats (like "12 AM" - could be hour or minute) default to hour format
• Large tables may take several minutes to update
• Recommended to backup database before running on production