94 lines
2.6 KiB
Markdown
94 lines
2.6 KiB
Markdown
# 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:
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```sql
|
|
-- 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
|
|
|