Update post_date_gmt with values from post_date

  • Resolved Glenn Ansley

    (@blepoxp)


    17 years, 7 months 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.

Viewing 7 replies - 1 through 7 (of 7 total)

  • 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 ??

    Thread Starter Glenn Ansley

    (@blepoxp)

    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?

    Sure, look here ??

    https://www.google.ca/search?hl=en&q=mysql+DATE_ADD&btnG=Search

    Thread Starter Glenn Ansley

    (@blepoxp)

    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)

    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

    Thread Starter Glenn Ansley

    (@blepoxp)

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

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

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Update post_date_gmt with values from post_date’ is closed to new replies.