We recently noticed that entries created prior the EE upgrade cannot be viewed as ‘Draft’ even when you Save As draft in EE5.
We found that the reason being the change in table columns structure. In EE2 channel_titles.edit_date is saved as DATETIME in BIGINT(14). In EE3+ channel_titles.edit_date is saved as unix timestamp in INT(10).
The publisher_titles.edit_date is still BIGINT(14) but a new publish/edit saves the edit_date with unix timestamp INT(10). This makes newer ‘Draft’ entry never has an edit_date bigger than the old ‘Open’ status entry’s edit_date.
The EE native update script ud_3_00_00.php has a function _update_entry_edit_date_format() to convert channel_titles.edit_date from BIGINT(14) to INT(10).
We had to manually run this script to convert the publisher_titles.edit_date.
ee()->db->query(“SET time_zone = ‘+0:00’;“);
ee()->db->query(“UPDATE exp_publisher_titles SET edit_date=UNIX_TIMESTAMP(edit_date) WHERE edit_date > 10000000000;“);
ee()->db->query(“SET time_zone = @@global.time_zone;“);