Make WordPress Core

Opened 15 years ago

Closed 8 years ago

#8885 closed defect (bug) (wontfix)

get_posts() should default orderby post_date_gmt

Reported by: caorongjin's profile caorongjin Owned by:
Milestone: Priority: normal
Severity: normal Version: 2.7
Component: Query Keywords: has-patch needs-unit-tests schema-change needs-refresh
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 13 years ago.

Download all attachments as: .zip

Change History (28)

#1 @Denis-de-Bernardy
15 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.

#2 @ryan
15 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.

#3 @Denis-de-Bernardy
15 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

#4 follow-up: @bi0xid
15 years ago

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

#5 in reply to: ↑ 4 @Otto42
15 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.

#6 @Viper007Bond
15 years ago

+1 as well.

#7 @janeforshort
15 years ago

  • Milestone changed from 2.8 to Future Release

Punting due to feature freeze. Reconsider with next release.

#8 @Denis-de-Bernardy
15 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

#9 @Denis-de-Bernardy
15 years ago

  • Component changed from General to Optimization

#10 @ryan
14 years ago

  • Milestone changed from 2.9 to 3.0

#11 @mattrude
14 years ago

  • Cc m@… added

#12 @hakre
14 years ago

  • Keywords needs-patch added

#13 @hakre
14 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.

@solarissmoke
13 years ago

#14 @solarissmoke
13 years ago

  • Keywords has-patch added; needs-patch removed

+1 to this, patch is simple

#15 @Viper007Bond
13 years ago

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

#16 @Denis-de-Bernardy
13 years ago

  • Keywords needs-patch added; has-patch removed

#18 follow-ups: @Denis-de-Bernardy
13 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 13 years ago by Denis-de-Bernardy (previous) (diff)

#19 in reply to: ↑ 18 @Viper007Bond
13 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.

#20 in reply to: ↑ 18 @solarissmoke
13 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.

#21 in reply to: ↑ description @lloydbudd
13 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.

#22 @nacin
10 years ago

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

#23 @wonderboymusic
10 years ago

  • Keywords schema-change added; needs-testing removed

#24 @chriscct7
8 years ago

  • Keywords needs-refresh added

This ticket was mentioned in Slack in #core by peterwilsoncc. View the logs.


8 years ago

#26 @peterwilsoncc
8 years ago

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.

#27 @peterwilsoncc
8 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.