WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Update post_date_gmt with values from post_date (8 posts)

  1. Glenn Ansley
    Member
    Posted 7 years ago #

    Hi, Would someone be able to help me form a query to replace the (blank) value in post_date_gmt with the value stored in post_date? I have trouble combining sql statements. I'm working in phpmyadmin's sql window. I need to do the following.

    1) Select all posts that have a 0 value in the post_date_gmt field of wp_posts.

    SELECT ID, post_date FROM wp_posts WHERE post_date_gmt = 0

    2) Update the post_date_gmt with the value from post_date where the post id for post_date is equal to the post id for post_date_gmt.

    I am having trouble with the second query.

    Thanks!

    EDIT: If I could add +3 hours to the post_date_gmt, that would be great... but its not essential.

  2. Andrew Ozz
    WordPress Dev
    Posted 7 years ago #

    I'm not that good with SQL, but you will need to do UPDATE, not SELECT to change the dates. Looking at upgrade-functions.php (in WP) the syntax is something like:

    UPDATE wp_posts WHERE post_date_gmt = 0 SET post_date_gmt = DATE_ADD(post_date, INTERVAL '03:00' HOUR_MINUTE) LIMIT 1

    Try it with LIMIT 1 first to see if it works right. And of course you need to have db backup :)

  3. Glenn Ansley
    Member
    Posted 7 years ago #

    Okay, I'll try that, but can I get the logic first. (I like to learn as I do).

    I understand the UPDATE and the WHERE. Is the DATE_ADD a mysql function?

  4. Andrew Ozz
    WordPress Dev
    Posted 7 years ago #

  5. Glenn Ansley
    Member
    Posted 7 years ago #

    I'm getting the following error

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE post_date_gmt = 0 SET post_date_gmt = DATE_ADD(post_

    It looks like the UPDATE query is being cut off at post_ (Or the error only repeats so many characters of my query)

  6. Andrew Ozz
    WordPress Dev
    Posted 7 years ago #

    Hmmm it stops at WHERE, perhaps the WHERE has to come after the SET, like that:

    UPDATE wp_posts SET post_date_gmt = DATE_ADD(post_date, INTERVAL '03:00' HOUR_MINUTE) WHERE post_date_gmt = 0 LIMIT 1

  7. Glenn Ansley
    Member
    Posted 7 years ago #

    Worked like a charm. Thanks so much for your help.

  8. freegambit
    Member
    Posted 7 years ago #

    Finally! Was cleaning out old blogger import issues and I realized that there were a bunch of missing gmt times!

Topic Closed

This topic has been closed to new replies.

About this Topic