gsrohde
commented
8 years ago Category 1: Bulk-uploaded traits [1353 out of 32504 rows (~4%)]
Description: The bulk upload wizard sets the date without setting the dateloc or timeloc values (this should be fixed). Moreover, it sets the date value by taking the date string given in the CSV file, interpreting this as midnight in Illinois time on that date, and then converting to the corresponding UTC time. It should be normalized to midnight site time, converted to UTC time.
Note: Some of these weren't necessarily created with the bulk upload wizard. But most or all were created automatically in some way because most occur in groups where the created_at value varies by only a fraction of a second.
Query for viewing problem rows:
SELECT
t.id,
date,
sitename,
s.city,
state,
s.country,
checked,
u.name as creator,
t.created_at,
t.updated_at
FROM
traits t
LEFT JOIN sites s ON s.id = t.site_id
LEFT JOIN users u ON u.id = t.user_id
WHERE
t.created_at IS NOT NULL
AND date IS NOT NULL
AND time IS NULL
AND date_year IS NULL
AND date_month IS NULL
AND date_day IS NULL
AND time_hour IS NULL
AND time_minute IS NULL
AND dateloc IS NULL
AND timeloc IS NULL
ORDER BY created_at, updated_at;Solution: Once the time_zone column has been added to the sites table, an update statement can be run to correct the date column values and set the dateloc and timeloc column values to 5 and 9, respectively.
SQL for fixing:
UPDATE traits
SET (dateloc, timeloc, date) = (
5,
9,
(
(
(date || ' UTC') :: TIMESTAMP WITH TIME ZONE AT TIME ZONE 'America/Chicago'
) || ' ' || (
SELECT
time_zone
FROM
sites s
WHERE
s.id = site_id
)
) :: TIMESTAMP WITH TIME ZONE AT TIME ZONE 'UTC'
)
WHERE
created_at IS NOT NULL
AND date IS NOT NULL
AND dateloc IS NULL
AND time IS NULL
AND date_year IS NULL
AND date_month IS NULL
AND date_day IS NULL
AND time_hour IS NULL
);
robkooper
dlebauer
I'll use this issue to describe categories of problem entries and my proposal for fixing them.