How can I update "post_modified" with a value from a custom field?

  • Resolved syrupcore

    (@syrupcore)


    10 years, 9 months ago

    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.