get_posts() should default orderby post_date_gmt

[caorongjin]

the function get_posts() in posts.php is defaulted to orderby post_date. The problem with this is if entries are added in differing timezones (e.g., I changed my system timezone after x number of posts have been made), then the sorting is incorrect. Since post_date_gmt is correct despite the timezone you are in, sorting based on this should be the default behavior.

[Denis-de-Bernardy]

It's a little trickier imo. What should happen when, say, you drop an hour due to daylight savings?

You post something at 12:55. And then, when it's 12:05, you post another, and the order will be messed up as well -- even using post_date_gmt.

[ryan]

This would require changing DB keys. That's not going to happen in 2.7.1 even if we decide to do this. Moving to 2.8.

[Denis-de-Bernardy]

But... is there any need to change it at all? surely there will always been edge cases, like the one he describes, or the one I highlighted, that will never work no matter what, unless we store all of this as timestamps or equivalent, i.e. the number of seconds since EPOCH or since the creation of the blog. That seems a bit overkill for a few edge cases. :-P

[bi0xid]

Maybe we must think using UTC instead of GMT to avoid problems in the future.

[Otto42]

Replying to Denis-de-Bernardy:

It's a little trickier imo. What should happen when, say, you drop an hour due to daylight savings?

You post something at 12:55. And then, when it's 12:05, you post another, and the order will be messed up as well -- even using post_date_gmt.

GMT does not do daylight savings. Never has, never will. So, post_date_gmt should never go backwards in time.

Replying to bi0xid:

Maybe we must think using UTC instead of GMT to avoid problems in the future.

UTC == GMT for all relevant cases. Okay, so they're technically different, but for our purposes they're the same thing (we're not dealing with time at the atomic level here...).

So yes, sorting by post_date_gmt instead of post_date makes sense. +1.

[Viper007Bond]

+1 as well.

[janeforshort]

Punting due to feature freeze. Reconsider with next release.

[Denis-de-Bernardy]

@Jane: Punting this back to 2.9, because a DB index problem that can trigger performance problems is hiding in the background. See: #9642

[hakre]

Setting to feature release because of a lack of patch while being opened for a longer time.

[solarissmoke]

+1 to this, patch is simple

[Viper007Bond]

The patch is simple, but the solution is apparently not. See Denis's comments above.

[Denis-de-Bernardy]

Here's a good read on the topic, FWIW:

​http://derickrethans.nl/storing-date-time-in-database.html

[Denis-de-Bernardy]

The short short version of the above breaks down to:

• post_date_gmt should contain the UTC time without any daylight savings or other adjustments. The raw stuff.
• a) If we guarantee that (not sure we currently do), then it's fine to order posts using it.
• b) If not, then we should enforce it, and can then safely order posts using it.

Unit tests tests are needed in either case.

[Viper007Bond]

Replying to Denis-de-Bernardy:

• post_date_gmt should contain the UTC time without any daylight savings or other adjustments. The raw stuff.

That's how it currently is. GMT is unaffected by DST. For all intents and purposes, GMT+0 == UTC.

[solarissmoke]

Replying to Denis-de-Bernardy:

post_date_gmt should contain the UTC time without any daylight savings or other adjustments. The raw stuff.

That's precisely what it is in the database for - so that we have a universally fixed timestamp to compare things with, separately from post_date which contains daylight savings adjustments.

GMT === UTC for all practical purposes. See ​http://en.wikipedia.org/wiki/Coordinated_Universal_Time.

[lloydbudd]

Replying to caorongjin:

the function get_posts() in posts.php is defaulted to orderby post_date. The problem with this is if entries are added in differing timezones (e.g., I changed my system timezone after x number of posts have been made), then the sorting is incorrect. Since post_date_gmt is correct despite the timezone you are in, sorting based on this should be the default behavior.

Each approach has its own problems.

The problem with the proposed solution is that the posts are no longer ordered by the displayed date.

"Fixing" the ordering with the resulting problem of the proposed solution is not obvious, particularly if you add multiple users and importantly scheduled posts. Imagine an admin changing the timezone info and then an editor trying to figure out what is going on.

The current problem is obvious to solve, the posts are displayed ordered by the displayed date.

[peterwilsoncc]

I'm going to close this off, wp_posts->post_date_gmt isn't indexed and the change has the potential to cause too many backward compatibility problems.

[peterwilsoncc]

I'm going to close this off, wp_posts->post_date_gmt isn't indexed and the change has the potential to cause too many backward compatibility problems.