Add 15 additional days to several dates en masse in mysql PHPmyadmin?-CodePudding

Hello I hope you can help me, I know I can do it manually but there are more than 200 users. I have these expiration dates to which I need to add an additional 15 days. How could I do it in bulk in mysql PHPmyadmin?

The data is in the table: wp_usermeta

CodePudding user response：

UPDATE wp_usermeta SET meta_value = DATE_ADD(meta_value, INTERVAL 15 DAY) WHERE meta_key = 'user_payment_expired_date';

CodePudding user response：

Since meta_value is a longtext, you can convert it to date and format it back afterwards.

UPDATE wp_usermeta SET meta_value = date_format(date_add(str_to_date(meta_value,'%Y-%m-%d'),interval 15 day), '%Y-%m-%d') WHERE meta_key = 'user_payment_expired_date';

CodePudding user response：

Have you considered executing a MySQL UPDATE statement? We'll have to assume that meta_datais a VARCHAR field, so we have to make a DATE out of it, then add 15 days to the value with DATEADD, then make it into a VARCHAR again with DATE_FORMAT and update the values in the table.

UPDATE wp_usermeta m SET m.meta_value = DATE_FORMAT(
                        DATE_ADD(
                          STR_TO_DATE(m.meta_value, '%Y-%m-%d')
                                , INTERVAL 15 DAY)
                    , '%Y-%m-%d')
WHERE meta_key = 'user_payment_expired_date';