Bulk add custom meta data from published date on a large catalog of posts in Wordpress

Question

I'm looking to follow this post in order to save WP post date data as custom meta for filtering purposes:

Wordpress: Exporting month and year of post into custom meta field

The problem is I also have a catalog of over 30k existing posts that I'd like to add the same custom meta based on month / year to. What would be the best way to update existing posts with the new meta fields based on published date?

Answer 1

For over 30k post, the fastest and most reliable way to do it is via 2 direct SQL Queries (via PhpMyAdmin), one for the post date YEAR and another one for the post date MONTH.

Always make a database backup (or at least in this case, only the wp_postmeta table).

Be sure that your database tables start with "wp_", if not, make the required changes below for wp_postmeta and wp_posts.

For the year (the meta key is set to 'published_year')

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, 'published_year', YEAR(p.post_date)
FROM wp_posts p
WHERE p.post_type = 'post';

For the month (the meta key is set to 'published_month'):

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, 'published_month', MONTH(p.post_date)
FROM wp_posts p
WHERE p.post_type = 'post';

The targeted post type is only "post", so if you want to make it work for pages too (or FOR others custom post types), replace:

WHERE p.post_type = 'post';

with (comma separated post types):

WHERE p.post_type IN ( 'post', 'page' );

---

This can also be done via a function using WPDB class like:

function bulk_add_published_year_custom_meta_data(){
    if( ! current_user_can('administrator') ) return; // Only for admins
    
    global $wpdb;

    $wpdb->query( "
        INSERT INTO {$wpdb->prefix}postmeta (post_id, meta_key, meta_value)
        SELECT p.ID, 'published_year', YEAR(p.post_date)
        FROM {$wpdb->prefix}posts p
        WHERE p.post_type = 'post';
    " );
}

function bulk_add_published_month_custom_meta_data(){
    if( ! current_user_can('administrator') ) return; // Only for admins
    
    global $wpdb;

    $wpdb->query( "
        INSERT INTO {$wpdb->prefix}postmeta (post_id, meta_key, meta_value)
        SELECT p.ID, 'published_month', MONTH(p.post_date)
        FROM {$wpdb->prefix}posts p
        WHERE p.post_type = 'post';
    " );
}

Code goes in functions.php file of your child theme.

To run this functions, you can add in functions.php file the following too, one at the time.

First, add:

bulk_add_published_year_custom_meta_data();

Save, then browse a page of your website. Remove and save.

Last, add:

bulk_add_published_month_custom_meta_data();

Save, then browse a page of your website. Remove and save.

Check that the meta data is set for all posts. Then remove all that code.

You are done.