MySql TIMESTAMPDIFF in Update fails

Question

I'm using MariaDB 10.4.25 and am trying to update some data in a WordPress table, but getting an error.

I can run the SELECT query without a problem, but then when I run the UPDATE, it throws a "Incorrect datetime value: month" error.

SELECT QUERY:

SELECT
 pm.post_id,
 pm.meta_key,
 pm.meta_value AS billing_interval,
 p1.meta_value AS schedule_start,
 p2.meta_value AS schedule_end,
 TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value) AS difference
FROM wp21_postmeta AS pm
JOIN wp21_posts AS p ON pm.post_id = p.ID
LEFT JOIN wp21_postmeta AS p1 ON pm.post_id = p1.post_id AND p1.meta_key = '_schedule_start'
LEFT JOIN wp21_postmeta AS p2 ON pm.post_id = p2.post_id AND p2.meta_key = '_schedule_end'
WHERE
 pm.meta_key = '_billing_interval'
 AND p.post_type = 'shop_subscription'
 AND pm.meta_value != TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value)
 AND TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value) != 0;

which correctly displays the values.

5319233 _billing_interval 1 2024年01月02日 20:10:53 2026年01月02日 20:10:53 2
5319234 _billing_interval 1 2024年01月02日 20:10:53 2027年01月02日 20:10:53 3
5319256 _billing_interval 1 2024年01月03日 20:48:04 2026年01月03日 20:48:04 2
5319257 _billing_interval 1 2024年01月03日 20:48:04 2027年01月03日 20:48:04 3
5319262 _billing_interval 1 2024年01月04日 19:05:46 2026年01月04日 19:05:46 2
5319263 _billing_interval 1 2024年01月04日 19:05:46 2027年01月04日 19:05:46 3
5319238 _billing_interval 1 2024年01月02日 20:12:07 2026年01月02日 20:12:07 2
5319239 _billing_interval 1 2024年01月02日 20:12:07 2027年01月02日 20:12:07 3

However, when I try the corresponding UPDATE I get the error:

UPDATE wp21_postmeta AS pm
JOIN wp21_posts AS p ON pm.post_id = p.ID
LEFT JOIN wp21_postmeta AS p1 ON pm.post_id = p1.post_id AND p1.meta_key = '_schedule_start'
LEFT JOIN wp21_postmeta AS p2 ON pm.post_id = p2.post_id AND p2.meta_key = '_schedule_end'
SET pm.meta_value = TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value)
WHERE
 pm.meta_key = '_billing_interval'
 AND p.post_type = 'shop_subscription'
 AND pm.meta_value != TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value);

Error code: 1292. Incorrect datetime value: 'month'

The error appears to be from the TIMESTAMPDIFF in the WHERE clause, since if I remark out that line, no error occurs. But I'm not clear why it works fine in the SELECT, but not the UPDATE. I also tried to convert the meta_values to datetime, but same error.

• Akina: Tips for asking a good Structured Query Language (SQL) question, #5 and #3.
• Rick James: File a bug report with mariadb.com.
• VBStarr: If a bug, does anyone have a good work-around for this?

Answer 1

Apparently this is a bug, which I will report.

The work-around that I can see is to use:

ROUND(DATEDIFF(p2.meta_value,p1.meta_value)/365,0)

instead of

TIMESTAMPDIFF(YEAR, p1.meta_value, p2.meta_value)

• VBStarr: Actually, this also failed.