Fix time zone/time stamps #134

MirandaEcho · 2020-06-30T13:25:13Z

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 · 2020-06-30T13:55:27Z

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 · 2020-06-30T14:49:36Z

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

benlk · 2020-06-30T14:52:47Z

It is currently set to America/Los_Angeles.

benlk · 2020-06-30T19:46:51Z

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 · 2020-06-30T19:47:53Z

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

joshdarby · 2020-06-30T19:51:25Z

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 · 2020-07-02T00:38:39Z

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 · 2020-07-02T00:41:26Z

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

I need to specify the Timezones.

benlk · 2020-07-02T00:49:20Z

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 · 2020-07-02T13:16:46Z

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 · 2020-07-02T19:42:57Z

@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 · 2020-07-07T14:19:26Z

Run on prod.

benlk self-assigned this Jun 30, 2020

benlk added this to Needs Review in SFPP-001 - Pre-Launch SoW Jun 30, 2020

MirandaEcho added the Priority: Critical label Jun 30, 2020

benlk added the Type: Question label Jun 30, 2020

benlk removed the Type: Question label Jun 30, 2020

benlk moved this from Needs Review to In progress in SFPP-001 - Pre-Launch SoW Jun 30, 2020

benlk changed the title ~~Check time zone/time stamps~~ Fix time zone/time stamps Jun 30, 2020

benlk closed this as completed Jul 7, 2020

benlk moved this from In progress to Done in SFPP-001 - Pre-Launch SoW Jul 7, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix time zone/time stamps #134

Fix time zone/time stamps #134

MirandaEcho commented Jun 30, 2020

benlk commented Jun 30, 2020 •

edited

MirandaEcho commented Jun 30, 2020

benlk commented Jun 30, 2020

benlk commented Jun 30, 2020

benlk commented Jun 30, 2020

joshdarby commented Jun 30, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

joshdarby commented Jul 2, 2020

benlk commented Jul 7, 2020

Fix time zone/time stamps #134

Fix time zone/time stamps #134

Comments

MirandaEcho commented Jun 30, 2020

benlk commented Jun 30, 2020 • edited

MirandaEcho commented Jun 30, 2020

benlk commented Jun 30, 2020

benlk commented Jun 30, 2020

benlk commented Jun 30, 2020

joshdarby commented Jun 30, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

benlk commented Jul 2, 2020

joshdarby commented Jul 2, 2020

benlk commented Jul 7, 2020

benlk commented Jun 30, 2020 •

edited