stash-box icon indicating copy to clipboard operation
stash-box copied to clipboard

Date accuracy on older edits is incorrect

Open peolic opened this issue 11 months ago • 0 comments

The date accuracy migration added in #810 (42_date_columns.up.sql) does not migrate the edit data.

Example from StashDB: https://stashdb.org/edits/140cf443-3985-4375-a20b-965a9dbbb82b (the input was '1996') Examples from my old dev database. I assume NULL date accuracy is DAY here.

SELECT /* e.id, */
    target_type,
    data['new_data'][d.field] AS old_date, data['old_data'][d.field||'_accuracy'] AS old_da,
    data['new_data'][d.field] AS new_date, data['new_data'][d.field||'_accuracy'] AS new_da
FROM edits e
JOIN (
    SELECT id, (CASE WHEN target_type = 'SCENE' THEN 'date' WHEN target_type = 'PERFORMER' THEN 'birthdate' END) field
    FROM edits
    WHERE target_type in ('SCENE', 'PERFORMER')
) d ON d.id = e.id
WHERE data['new_data'] ? d.field OR data['old_data'] ? d.field
ORDER BY e.created_at DESC, e.id ASC;
target_type old_date old_da new_date new_da
SCENE "2022-01-01" "DAY" "2022-01-01" "DAY"
PERFORMER "1995-06-13" "1995-06-13" "DAY"
SCENE "2022-01-01" "DAY" "2022-01-01" "YEAR"
SCENE "2022-01-01" "2022-01-01" "YEAR"
PERFORMER "1986-01-01" "1986-01-01" "YEAR"
PERFORMER "1985-12-01" "MONTH" "1985-12-01" "DAY"
PERFORMER "1985-12-01" "MONTH" "1985-12-01" "DAY"
PERFORMER "1992-03-01" "DAY" "1992-03-01" "MONTH"
PERFORMER "1992-03-15" "1992-03-15" "DAY"
PERFORMER "1985-12-01" "1985-12-01" "MONTH"
SCENE "2021-10-09" "2021-10-09"
PERFORMER "1990-01-01" "1990-01-01" "YEAR"

peolic avatar Jan 23 '25 15:01 peolic