0

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.

3

1 Answer 1

0

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)
1
  • Actually, this also failed.
    – VBStarr
    Feb 10 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.