WordPress.org

Make WordPress Core

Opened 7 years ago

Last modified 15 months ago

#8885 new defect (bug)

get_posts() should default orderby post_date_gmt

Reported by: caorongjin Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 2.7
Component: Query Keywords: has-patch needs-unit-tests schema-change
Focuses: performance Cc:

Description

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.

Attachments (1)

8885.diff (528 bytes) - added by solarissmoke 5 years ago.

Download all attachments as: .zip

Change History (24)

comment:1 @Denis-de-Bernardy7 years ago

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.

comment:2 @ryan7 years ago

  • Milestone changed from 2.7.1 to 2.8

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.

comment:3 @Denis-de-Bernardy7 years ago

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

comment:4 follow-up: @bi0xid7 years ago

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

comment:5 in reply to: ↑ 4 @Otto426 years ago

  • Version set to 2.7

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.

comment:6 @Viper007Bond6 years ago

+1 as well.

comment:7 @janeforshort6 years ago

  • Milestone changed from 2.8 to Future Release

Punting due to feature freeze. Reconsider with next release.

comment:8 @Denis-de-Bernardy6 years ago

  • Milestone changed from Future Release to 2.9

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

comment:9 @Denis-de-Bernardy6 years ago

  • Component changed from General to Optimization

comment:10 @ryan6 years ago

  • Milestone changed from 2.9 to 3.0

comment:11 @mattrude6 years ago

  • Cc m@… added

comment:12 @hakre6 years ago

  • Keywords needs-patch added

comment:13 @hakre6 years ago

  • Milestone changed from 3.0 to Future Release

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

@solarissmoke5 years ago

comment:14 @solarissmoke5 years ago

  • Keywords has-patch added; needs-patch removed

+1 to this, patch is simple

comment:15 @Viper007Bond5 years ago

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

comment:16 @Denis-de-Bernardy5 years ago

  • Keywords needs-patch added; has-patch removed

comment:18 follow-ups: @Denis-de-Bernardy5 years ago

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, we should enforce it, then we should enforce it, and can then safely order posts using it.

Unit tests tests are needed in either case.

Version 1, edited 5 years ago by Denis-de-Bernardy (previous) (next) (diff)

comment:19 in reply to: ↑ 18 @Viper007Bond5 years ago

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.

comment:20 in reply to: ↑ 18 @solarissmoke5 years ago

  • Keywords has-patch needs-testing added; needs-patch removed

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.

comment:21 in reply to: ↑ description @lloydbudd4 years ago

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.

comment:22 @nacin18 months ago

  • Component changed from Optimization to Query
  • Focuses performance added
  • Keywords needs-unit-tests added

comment:23 @wonderboymusic15 months ago

  • Keywords schema-change added; needs-testing removed
Note: See TracTickets for help on using tickets.