WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 3 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-testing needs-unit-tests
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 3 years ago.

Download all attachments as: .zip

Change History (23)

comment:1 Denis-de-Bernardy5 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 ryan5 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-Bernardy5 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: bi0xid5 years ago

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

comment:5 in reply to: ↑ 4 Otto425 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 Viper007Bond5 years ago

+1 as well.

comment:7 janeforshort5 years ago

  • Milestone changed from 2.8 to Future Release

Punting due to feature freeze. Reconsider with next release.

comment:8 Denis-de-Bernardy5 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-Bernardy5 years ago

  • Component changed from General to Optimization

comment:10 ryan4 years ago

  • Milestone changed from 2.9 to 3.0

comment:11 mattrude4 years ago

  • Cc m@… added

comment:12 hakre4 years ago

  • Keywords needs-patch added

comment:13 hakre4 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.

solarissmoke3 years ago

comment:14 solarissmoke3 years ago

  • Keywords has-patch added; needs-patch removed

+1 to this, patch is simple

comment:15 Viper007Bond3 years ago

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

comment:16 Denis-de-Bernardy3 years ago

  • Keywords needs-patch added; has-patch removed

comment:18 follow-ups: Denis-de-Bernardy3 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, then we should enforce it, and can then safely order posts using it.

Unit tests tests are needed in either case.

Last edited 3 years ago by Denis-de-Bernardy (previous) (diff)

comment:19 in reply to: ↑ 18 Viper007Bond3 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 solarissmoke3 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 lloydbudd3 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 nacin3 months ago

  • Component changed from Optimization to Query
  • Focuses performance added
  • Keywords needs-unit-tests added
Note: See TracTickets for help on using tickets.