211 lines
6.6 KiB
SQL
211 lines
6.6 KiB
SQL
-- Fix Malformed Time Entries in OptionsFlow_monthly
|
|
-- This script identifies and corrects time entries that are missing the hour component
|
|
-- Examples: "34 AM" should be "11:34 AM" or "9:34 AM"
|
|
|
|
-- Step 1: Identify malformed time entries
|
|
-- Times that match pattern: just digits followed by AM/PM (no colon, no hour)
|
|
-- Examples: "34 AM", "45 PM", "12 AM" (without hour)
|
|
|
|
SELECT
|
|
COUNT(*) as malformed_count,
|
|
COUNT(DISTINCT "CreatedDate") as affected_dates
|
|
FROM "OptionsFlow_monthly"
|
|
WHERE "CreatedTime" IS NOT NULL
|
|
AND "CreatedTime"::text ~ '^\d{1,2}\s*(AM|PM)$' -- Just number + AM/PM, no colon
|
|
AND "CreatedTime"::text !~ '^\d{1,2}:\d{2}' -- No colon present
|
|
AND "CreatedTime"::text !~ '^\d{1,2}:\d{2}:\d{2}'; -- No full time format
|
|
|
|
-- Step 2: Show sample malformed entries
|
|
SELECT
|
|
"CreatedDate",
|
|
"CreatedTime",
|
|
"Symbol",
|
|
COUNT(*) as occurrences
|
|
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}'
|
|
GROUP BY "CreatedDate", "CreatedTime", "Symbol"
|
|
ORDER BY "CreatedDate" DESC, occurrences DESC
|
|
LIMIT 20;
|
|
|
|
-- Step 3: Fix malformed times by inferring hour from context
|
|
-- Strategy: Look at surrounding records on the same date to infer the hour
|
|
-- If no context available, use a default based on the minute value:
|
|
-- - Minutes 0-30: likely morning (9-11 AM) or afternoon (1-3 PM)
|
|
-- - Minutes 31-59: likely late morning (10-11 AM) or afternoon (2-4 PM)
|
|
|
|
-- Create a temporary table with corrected times
|
|
CREATE TEMP TABLE time_fixes AS
|
|
WITH malformed AS (
|
|
SELECT
|
|
ctid, -- Physical row identifier
|
|
"CreatedDate",
|
|
"CreatedTime",
|
|
"Symbol",
|
|
-- Extract the minute value
|
|
CASE
|
|
WHEN "CreatedTime"::text ~ '^(\d{1,2})\s*(AM|PM)$'
|
|
THEN (REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})\s*(AM|PM)$', '\1'))::int
|
|
ELSE NULL
|
|
END AS minute_only,
|
|
CASE
|
|
WHEN "CreatedTime"::text ~ '^(\d{1,2})\s*(AM|PM)$'
|
|
THEN REGEXP_REPLACE("CreatedTime"::text, '^(\d{1,2})\s*(AM|PM)$', '\2')
|
|
ELSE NULL
|
|
END AS am_pm
|
|
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}'
|
|
),
|
|
context_hours AS (
|
|
-- Try to infer hour from nearby records on the same date
|
|
SELECT DISTINCT ON (m.ctid)
|
|
m.ctid,
|
|
m."CreatedDate",
|
|
m."CreatedTime",
|
|
m.minute_only,
|
|
m.am_pm,
|
|
-- Look for nearby records with proper time format
|
|
CASE
|
|
WHEN nearby."CreatedTime" IS NOT NULL
|
|
AND nearby."CreatedTime"::text ~ '^\d{1,2}:\d{2}'
|
|
THEN
|
|
CASE
|
|
WHEN nearby."CreatedTime"::text ~ '^(\d{1,2}):\d{2}\s*(AM|PM)$'
|
|
THEN (REGEXP_REPLACE(nearby."CreatedTime"::text, '^(\d{1,2}):\d{2}\s*(AM|PM)$', '\1'))::int
|
|
ELSE NULL
|
|
END
|
|
ELSE NULL
|
|
END AS inferred_hour
|
|
FROM malformed m
|
|
LEFT JOIN LATERAL (
|
|
SELECT "CreatedTime"
|
|
FROM "OptionsFlow_monthly" n
|
|
WHERE n."CreatedDate" = m."CreatedDate"
|
|
AND n."CreatedTime" IS NOT NULL
|
|
AND n."CreatedTime"::text ~ '^\d{1,2}:\d{2}'
|
|
AND n."Symbol" = m."Symbol" -- Same symbol for better context
|
|
ORDER BY ABS(
|
|
CASE
|
|
WHEN n."CreatedTime"::text ~ '^(\d{1,2}):(\d{2})\s*(AM|PM)$'
|
|
THEN (REGEXP_REPLACE(n."CreatedTime"::text, '^(\d{1,2}):(\d{2})\s*(AM|PM)$', '\2'))::int
|
|
ELSE 999
|
|
END - m.minute_only
|
|
)
|
|
LIMIT 1
|
|
) nearby ON TRUE
|
|
),
|
|
fixed_times AS (
|
|
SELECT
|
|
tf.ctid,
|
|
tf."CreatedDate",
|
|
tf."CreatedTime" as original_time,
|
|
tf.minute_only,
|
|
tf.am_pm,
|
|
COALESCE(tf.inferred_hour,
|
|
-- Default hour based on minute and AM/PM
|
|
CASE
|
|
WHEN tf.am_pm = 'AM' THEN
|
|
CASE
|
|
WHEN tf.minute_only <= 30 THEN 9 -- Early morning: 9 AM
|
|
ELSE 10 -- Late morning: 10 AM
|
|
END
|
|
ELSE -- PM
|
|
CASE
|
|
WHEN tf.minute_only <= 30 THEN 1 -- Early afternoon: 1 PM
|
|
ELSE 2 -- Late afternoon: 2 PM
|
|
END
|
|
END
|
|
) as fixed_hour,
|
|
-- Build the corrected time string
|
|
CASE
|
|
WHEN tf.inferred_hour IS NOT NULL THEN
|
|
LPAD(tf.inferred_hour::text, 2, '0') || ':' || LPAD(tf.minute_only::text, 2, '0') || ':00 ' || tf.am_pm
|
|
ELSE
|
|
LPAD(
|
|
CASE
|
|
WHEN tf.am_pm = 'AM' THEN
|
|
CASE
|
|
WHEN tf.minute_only <= 30 THEN 9
|
|
ELSE 10
|
|
END
|
|
ELSE
|
|
CASE
|
|
WHEN tf.minute_only <= 30 THEN 1
|
|
ELSE 2
|
|
END
|
|
END::text,
|
|
2, '0'
|
|
) || ':' || LPAD(tf.minute_only::text, 2, '0') || ':00 ' || tf.am_pm
|
|
END as fixed_time
|
|
FROM context_hours tf
|
|
)
|
|
SELECT * FROM fixed_times;
|
|
|
|
-- Step 4: Preview the fixes (run this first to verify)
|
|
SELECT
|
|
"CreatedDate",
|
|
original_time,
|
|
fixed_time,
|
|
COUNT(*) as count
|
|
FROM time_fixes
|
|
GROUP BY "CreatedDate", original_time, fixed_time
|
|
ORDER BY "CreatedDate" DESC, count DESC
|
|
LIMIT 50;
|
|
|
|
-- Step 5: Apply the fixes (UNCOMMENT TO EXECUTE)
|
|
-- WARNING: This will update the database. Review the preview above first!
|
|
|
|
/*
|
|
UPDATE "OptionsFlow_monthly" ofm
|
|
SET "CreatedTime" = tf.fixed_time
|
|
FROM time_fixes tf
|
|
WHERE ofm.ctid = tf.ctid
|
|
AND ofm."CreatedTime" = tf.original_time;
|
|
*/
|
|
|
|
-- Step 6: Verify the fixes
|
|
SELECT
|
|
COUNT(*) as remaining_malformed
|
|
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}';
|
|
|
|
-- Alternative: More aggressive fix for times like ":34 AM" (missing hour, has colon)
|
|
-- This handles cases where the hour was completely missing
|
|
UPDATE "OptionsFlow_monthly"
|
|
SET "CreatedTime" =
|
|
CASE
|
|
-- Pattern: ":34 AM" -> "9:34 AM" (default to 9 AM for morning)
|
|
WHEN "CreatedTime"::text ~ '^:\d{1,2}\s*AM$' THEN
|
|
'9:' || LPAD(REGEXP_REPLACE("CreatedTime"::text, '^:(\d{1,2})\s*AM$', '\1'), 2, '0') || ':00 AM'
|
|
-- Pattern: ":34 PM" -> "1:34 PM" (default to 1 PM for afternoon)
|
|
WHEN "CreatedTime"::text ~ '^:\d{1,2}\s*PM$' THEN
|
|
'1:' || LPAD(REGEXP_REPLACE("CreatedTime"::text, '^:(\d{1,2})\s*PM$', '\1'), 2, '0') || ':00 PM'
|
|
ELSE "CreatedTime"
|
|
END
|
|
WHERE "CreatedTime" IS NOT NULL
|
|
AND (
|
|
"CreatedTime"::text ~ '^:\d{1,2}\s*AM$'
|
|
OR "CreatedTime"::text ~ '^:\d{1,2}\s*PM$'
|
|
);
|
|
|
|
-- Final verification query
|
|
SELECT
|
|
"CreatedDate",
|
|
"CreatedTime",
|
|
COUNT(*) as count
|
|
FROM "OptionsFlow_monthly"
|
|
WHERE "CreatedTime" IS NOT NULL
|
|
AND (
|
|
"CreatedTime"::text ~ '^\d{1,2}\s*(AM|PM)$' -- Just number + AM/PM
|
|
OR "CreatedTime"::text ~ '^:\d{1,2}\s*(AM|PM)$' -- Colon + number + AM/PM
|
|
)
|
|
GROUP BY "CreatedDate", "CreatedTime"
|
|
ORDER BY count DESC
|
|
LIMIT 20;
|
|
|