Fix time zone/time stamps

MirandaEcho commented 4 years ago

Time stamps (and in some cases affecting publication dates) are SEVEN HOURS off. It looks like the new site translated original time stamps into GMT, but the new site is running in Pacific Time. So, anything that was migrated by developers is off by SEVEN HOURS. Can we shift time stamps site-wide by seven hours? (It would mean that we will have to reset all of the newer stories manually, but I can't think of another option. . . )

MS — Combined this with a duplicate entry from Brian:

Stories migrated from old site do not always retain their original date/time of publication. When re-assigning story categories, I noticed that storied dated April 13 on the old site are dated April 14 -- 7 hours later -- on the Wordpress.

Example: https://sfpublicpress.org/news/2020-04/sf-cites-equipment-shortages-in-covid-19-testing-as-thousands-of-swabs-arrive-in-town Old site date/time: April 13, 7:07pm

Wordpress date/time: April 14, 2:07 am

benlk commented 4 years ago

We can put the new site into GMT, but from now on SFPP will need to think about what time it is in GMT when scheduling posts to be posted in WP, and they'll need to keep track of the changing GMT offset as the US switches into and out of DST.

As an alternative:

The function largo_time() that's used to output dates is pluggable, thankfully, so if SFPP wanted to keep the site's time in the America/Los_Angeles time zone, we could write something that fudges the date on imported posts by subtracting 7h. There are ~2 ways to do this:

for posts published before a certain date/time: they'd need to keep the 7h adjustment in mind
for posts with the migration metadata: the flag is not obvious; we may want to make some sort of metabox that only appears in the editor if a post was migrated, and warns SFPP users about this condition
for posts not edited after a certain date/time: they'd need to check and possibly set the date if they're editing a post

The basic time-fudging might take 1-2h; adding a "what is going on here" display metabox would take another hour.

MirandaEcho commented 4 years ago

@benlk to clarify, what time zone is the site currently set to?

benlk commented 4 years ago

It is currently set to America/Los_Angeles.

benlk commented 4 years ago

The largo_time cosmetic fix doesn't affect the datetime used for the date archives.

We could write a script to update it based on whether the publish date was before the date of the last import.

Then we'd just have to grok the date math and write a thing to do it, stealing some code from https://github.com/INN/workday-author-fixer/ for processing.

benlk commented 4 years ago

@joshdarby any other ideas for bulk shifting of publish dates for posts by 7h relative to the current publish date?

joshdarby commented 4 years ago

any other ideas for bulk shifting of publish dates for posts by 7h relative to the current publish date?

@benlk Not any good ones 😬

I think borrowing the general idea of the Workday author fix script and just batch replacing dates might be the best idea

benlk commented 4 years ago

linked post in example is post 4036, http://sfpublicpress.flywheelsites.com/s-f-cites-equipment-shortages-in-covid-19-testing-as-thousands-of-swabs-arrive-in-town/ .

Old Site: Apr 13 2020 - 7:07pm
Present staging: displayed datetime is 04.14.2020, and the backend date is April 14, 2020 2:07 am, and the db dates are:
```
post_date             | 2020-04-14 02:07:22
post_date_gmt         | 2020-04-14 02:07:22
```
After running the following script to completion on local, the displayed date is 04.13.2020, and the backend date is April 13, 2020 12:07 pm, and the db dates are:
```
post_date             | 2020-04-13 12:07:22
post_date_gmt         | 2020-04-14 02:07:22 
```

Adjusting by minus 7 hours gives us a difference of 2h, because I'm in GMT+5?

<?php
if ( file_exists('.wordpress/wp-load.php') ) {
    require_once('.wordpress/wp-load.php');
} else {
    require_once('./wp-load.php');
}

$query_args = array(
    'posts_per_page' => 1000,
);

$page = 1;

$posts = get_posts( $query_args );

printf(
    '<h1>posts for page %1$s</h1>',
    $page
);

echo '<ul>';

foreach ( $posts as $post ) {

    /*
     * because the WP site has only ever been in America/Los_Angeles,
     * posts written in WordPress should have different times for GMT and local time,
     * and imported posts whose post dates were not edited in WordPress will not have different times.
     */
    if ( $post->post_date === $post->post_date_gmt ) {
        echo '<li>';
        echo 'post ' . $post->ID . ': <br/>';
        printf(
            'Found equal post dates: post_date=%1$s; post_date_gmt=%2$s',
            $post->post_date,
            $post->post_date_gmt
        );
        echo '<br/>';

        $post_date = new DateTime( $post->post_date );
        $post_date_gmt = new DateTime( $post->post_date_gmt );
        // what should the new time be?
        // thw wrong time is 7h later
        // so we must subtract 7h from the post publish date, so that the GMT date remains the same
        $minus_seven = $post_date->sub( new DateInterval( 'PT7H' ) );
        printf(
            'from the post date to the GMT post date: %1$s',
            human_time_diff(
                $minus_seven->getTimestamp(),
                $post_date_gmt->getTimestamp(),
            )
        );
        // but human_time_diff does not put a sign on that difference.

        // and then it is time to update the posts
        $array = array(
            'ID' => $post->ID,
            // according to https://developer.wordpress.org/reference/functions/mysql2date/ this is the proper format for mysql with wordpress
            // it is not a named constant in https://www.php.net/manual/en/class.datetimeinterface.php
            'post_date' => wp_date( 'Y-m-d H:i:s', $minus_seven->getTimestamp() ),
        );

        printf(
            '<pre><code>%1$s</code></pre>',
            esc_html( var_export( $array, true ) )
        );

        $return = wp_update_post( $array );
        printf(
            '<p>Now updating the date. If it was a success, this will be a post ID, otherwise a WP_Error. %2$s </p><pre><code>%1$s</code></pre>',
            esc_html( var_export( $return, true ) ),
            ( is_numeric( $return ) ) ? '&#128154; SUCCESS' : '&#128721; ERROR' 
        );

        echo '</li>';
    }
}

echo '</ul>';

benlk commented 4 years ago

        $post_date = new DateTime( $post->post_date );
        $post_date_gmt = new DateTime( $post->post_date_gmt );

I need to specify the Timezones.

benlk commented 4 years ago

Revised code:

<?php
if ( file_exists('.wordpress/wp-load.php') ) {
    require_once('.wordpress/wp-load.php');
} else {
    require_once('./wp-load.php');
}

$query_args = array(
    'posts_per_page' => 1000,
);

$page = 1;

$posts = get_posts( $query_args );

printf(
    '<h1>posts for page %1$s</h1>',
    $page
);

echo '<ul>';

foreach ( $posts as $post ) {

    /*
     * because the WP site has only ever been in America/Los_Angeles,
     * posts written in WordPress should have different times for GMT and local time,
     * and imported posts whose post dates were not edited in WordPress will not have different times.
     */
    if ( $post->post_date === $post->post_date_gmt ) {
        echo '<li>';
        echo 'post ' . $post->ID . ': <br/>';
        printf(
            'Found equal post dates: post_date=%1$s; post_date_gmt=%2$s',
            $post->post_date,
            $post->post_date_gmt
        );
        echo '<br/>';

        $post_date = new DateTime( $post->post_date, new DateTimeZone( 'America/Los_Angeles' )  );
        $post_date_gmt = new DateTime( $post->post_date_gmt, new DateTimeZone( '+0000' ) );
        // what should the new time be?
        // thw wrong time is 7h later
        // so we must subtract 7h from the post publish date, so that the GMT date remains the same
        $minus_seven = $post_date->sub( new DateInterval( 'PT7H' ) );
        printf(
            'from the -7h post date to the GMT post date: %1$s',
            human_time_diff(
                $minus_seven->getTimestamp(),
                $post_date_gmt->getTimestamp(),
            )
        );
        // but human_time_diff does not put a sign on that difference.

        // and then it is time to update the posts
        $array = array(
            'ID' => $post->ID,
            // according to https://developer.wordpress.org/reference/functions/mysql2date/ this is the proper format for mysql with wordpress
            // it is not a named constant in https://www.php.net/manual/en/class.datetimeinterface.php
            'post_date' => wp_date( 'Y-m-d H:i:s', $minus_seven->getTimestamp() ),
        );

        printf(
            '<pre><code>%1$s</code></pre>',
            esc_html( var_export( $array, true ) )
        );

        $return = wp_update_post( $array );
        printf(
            '<p>Now updating the date. If it was a success, this will be a post ID, otherwise a WP_Error. %2$s </p><pre><code>%1$s</code></pre>',
            esc_html( var_export( $return, true ) ),
            ( is_numeric( $return ) ) ? '&#128154; SUCCESS' : '&#128721; ERROR' 
        );

        echo '</li>';
    }
}

echo '</ul>';

With this run, post 4036 has a backend date of April 13, 2020 7:07 pm and frontend date of 04.13.2020

@joshdarby does this work on your computer, and do you see any problems in it?

benlk commented 4 years ago

Updated version:

adds pagination so we can iterate 1k posts at a time
also checks posts that are not publish

<?php
if ( file_exists('.wordpress/wp-load.php') ) {
    require_once('.wordpress/wp-load.php');
} else {
    require_once('./wp-load.php');
}

$query_args = array(
    'posts_per_page' => 1000,
    'post_status' => 'any'
);

if ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) {
    $page = (int) $_GET['page'];
    $query_args['paged'] = $page;
} else {
    $page = 1;
}

$posts = get_posts( $query_args );

printf(
    '<h1>%1$s posts for page %2$s</h1>',
    count( $posts ),
    $page
);

echo '<ul>';

foreach ( $posts as $post ) {

    /*
     * because the WP site has only ever been in America/Los_Angeles,
     * posts written in WordPress should have different times for GMT and local time,
     * and imported posts whose post dates were not edited in WordPress will not have different times.
     */
    if ( $post->post_date === $post->post_date_gmt ) {
        echo '<li>';
        echo 'post ' . $post->ID . ': <br/>';
        printf(
            'Found equal post dates: post_date=%1$s; post_date_gmt=%2$s',
            $post->post_date,
            $post->post_date_gmt
        );
        echo '<br/>';

        $post_date = new DateTime( $post->post_date, new DateTimeZone( 'America/Los_Angeles' )  );
        $post_date_gmt = new DateTime( $post->post_date_gmt, new DateTimeZone( '+0000' ) );
        // what should the new time be?
        // thw wrong time is 7h later
        // so we must subtract 7h from the post publish date, so that the GMT date remains the same
        $minus_seven = $post_date->sub( new DateInterval( 'PT7H' ) );
        printf(
            'from the -7h post date to the GMT post date: %1$s',
            human_time_diff(
                $minus_seven->getTimestamp(),
                $post_date_gmt->getTimestamp(),
            )
        );
        // but human_time_diff does not put a sign on that difference.

        // and then it is time to update the posts
        $array = array(
            'ID' => $post->ID,
            // according to https://developer.wordpress.org/reference/functions/mysql2date/ this is the proper format for mysql with wordpress
            // it is not a named constant in https://www.php.net/manual/en/class.datetimeinterface.php
            'post_date' => wp_date( 'Y-m-d H:i:s', $minus_seven->getTimestamp() ),
        );

        printf(
            '<pre><code>%1$s</code></pre>',
            esc_html( var_export( $array, true ) )
        );

        $return = wp_update_post( $array );
        printf(
            '<p>Now updating the date. If it was a success, this will be a post ID, otherwise a WP_Error. %2$s </p><pre><code>%1$s</code></pre>',
            esc_html( var_export( $return, true ) ),
            ( is_numeric( $return ) ) ? '&#128154; SUCCESS' : '&#128721; ERROR' 
        );

        echo '</li>';
    }
}

echo '</ul>';

if ( ! empty( $posts ) ) {
    printf(
        '<a href="%1$s%2$s%3$s">Next page</a>',
        basename( __FILE__ ),
        '?page=',
        $page + 1
    );
} else {
    echo "no more posts";
}

joshdarby commented 4 years ago

@benlk I ran it for all 3,xxx posts on my local environment and didn't see any errors. Seems to work as expected!

benlk commented 4 years ago

Run on prod.

INN / umbrella-sfpublicpress

Fix time zone/time stamps #134