Mass Editing Posts' Timestamps to Attachment Dates


I had migrated a portfolio website to WordPress, and used the Add Media from Server plugin to make the various files into attachments. I made sure to check the option that makes the attachment's date equal to the file's timestamp.

Then I added the various posts to the site, and used the Change Attachment Parent plugin to attach the various media files to their proper parent post.

The final step was to set each post's publish time to the date of that post's newest attachment. A bit of MySQL wizardry does this:

update wp_posts as pp
      inner join(
        select post_parent as pic_parent, ID as pic_id, max(post_date) as pic_date
        from wp_posts
        where post_parent>0 and post_type='attachment'
        group by post_parent
      ) as attach
      on pp.ID=attach.pic_parent
   set pp.post_date=attach.pic_date, pp.post_date_gmt=attach.pic_date;