Support » Fixing WordPress » How can I update "post_modified" with a value from a custom field?

  • Resolved syrupcore

    (@syrupcore)


    I’m working on a site that has a Press Release CPT. Using ACF, I’ve added a “Press Release Date” date picker field. Recently, a content author spent two days manually importing a few hundred old press releases, manually setting the date on each.

    Awesome. Except, each one of those entries now has a created and modified date of 3 days ago. Not so good, especially when it comes to search results.

    How can I update “post_modified” and “post_created” with a value stored in wp_postmeta?

    I know I need to store a reference to the custom field value and then use something like UNIX_TIMESTAMP to convert the values from “20130219” to “2013-02-19 12:0:00”. I just don’t know how!

    I got this far but am at a bit of a loss for next steps, such is my SQL fu.

    SELECT
    	wp_posts.id,
    	wp_posts.post_date,
    	wp_posts.post_modified,
    	wp_postmeta.meta_key,
    	wp_postmeta.meta_value
    
    FROM
    	wp_posts
    JOIN
    	wp_postmeta ON wp_postmeta.post_id = wp_posts.id
    WHERE
    	wp_posts.post_type = 'press_release'
    AND
    	wp_postmeta.meta_key = 'pr_date'
    
    UPDATE ???

    Thank you!

Viewing 1 replies (of 1 total)
  • Thread Starter syrupcore

    (@syrupcore)

    Sorted it out. Answer here in case someone comes looking for it via the googles:

    UPDATE
    	wp_posts, wp_postmeta
    	SET
    		post_date = STR_TO_DATE(wp_postmeta.meta_value, '%Y%c%d'),
    		post_modified = STR_TO_DATE(wp_postmeta.meta_value, '%Y%c%d')
    
    WHERE
    	wp_postmeta.post_id = wp_posts.id
    AND
    	wp_posts.post_type = 'press_release'
    AND
    	wp_postmeta.meta_key = 'pr_date' #the name of my custom field
Viewing 1 replies (of 1 total)
  • The topic ‘How can I update "post_modified" with a value from a custom field?’ is closed to new replies.