Sorting posts by meta values: 2 different orders

I think it should be able to hook into the posts_clauses filter to add a field to your sql query. 

add_filter( 'posts_clauses', 'intercept_query_clauses', 20, 1 );

function intercept_query_clauses( $pieces ){
    global $wpdb;
    $pieces['fields'].=', (IF(UNIX_TIMESTAMP()>='.$wpdb->postmeta.'.meta_value,'.$wpdb->postmeta.'.meta_value,(UNIX-TIMESTAMP() - '.$wpdb->postmeta.'.meta_value)+UNIX_TIMESTAMP('2099-01-01'))) as timestamp_direction';
    $pieces['orderby']='timestamp_direction ASC';
    return $pieces;
}

The logic is as follows:

Let’s say you have some timestamps in your metafield

event 1 (way past) : 9999

event 2 (past) : 10000

timestamp now: 11111

event 3 (future): 12222

event 4 (further into future): 13333

timestamp of 2099-01-01: 100000

We build a temporary field in our query that has the timestamp from the metafield if it is higher than the current timestamp. If the metafield is lower than the metafield, it first gets subtracted from the current timestamp and then the timestamp of a date way way into the future. After that, we have the following values:

event 1: 101112

event 2: 101111

event 3: 12222

event 4: 13333

Now ordering this by timestamp_direction gives us the order that you want: first the events in the future ordered ASC, then the past events ordered DESC:

event 3: 12222

event 4: 13333

event 2: 101111

event 1: 101112

Related Posts:

  1. Sorting Posts by custom field
  2. WordPress Comments sort by custom meta key
  3. Understanding the orderby in WP_Query?
  4. WP Group posts by year(desc) > month(desc) > date(asc)
  5. How to use a dropdown to filter posts by custom field
  6. Order 2 meta_queries differently in WP_Query?
  7. Sort posts on custom field AND after that sort on date?
  8. Wrap group of wp query posts to parent div by date/year
  9. query_posts with sorting on a custom datestamp
  10. WP_Query – order with usort by custom meta
  11. Query to sort a list by meta key first (if it exists), and show remaining posts without meta key ordered by title
  12. Can wp_query return posts meta in a single request?
  13. Meta query with string starting like pattern
  14. compare meta_query in get_posts arguments
  15. How to get order of posts?
  16. How to get Page/Post Gallery attachment images in order they are set in backend using WP_Query()?
  17. WP Query orderby meta key natural sort?
  18. Sorting a query by custom field date
  19. meta_query where value is equal to given value
  20. Get_post() with meta_key when compare is a date
  21. querying with custom meta field with meta_query
  22. Group posts by custom field
  23. Order by meta_value_num DESC and meta_value ASC on WP 4.0
  24. getting posts and number by specific meta value in multiple meta
  25. How to query using a combination of custom_field values?
  26. Revolution Slider Orderby Two Custom Fields
  27. WordPress altering my custom query, How to fix it?
  28. Order by meta_key in custom post type doesn’t affect the query
  29. Woocommerce custom loop to show all the products [closed]
  30. How to show only one post for each categories of taxonomy of custom post that contains a specific custom field
  31. WordPress: Keep order of query_posts list of post ID’s using post__in
  32. wp_query with multiple custom fields
  33. Sort by two dates. Default entry date and custom field if present
  34. Filtering posts by custom field value not working
  35. Display link to taxonomy archive only if it has posts with certain custom field values
  36. getting posts and number by specific meta value in multiple meta
  37. Order Posts by meta value AND published date
  38. How to orderby meta_value_num with dollar ($) sign
  39. wp_query add arguments using array_push if variable met
  40. Group WP_Query by meta_key date
  41. How To Fix WP Query Returns Results But Shouldn’t?
  42. Display custom field meta outside loop, site wide
  43. Orderby is working with one query but not with other
  44. Extend product search with meta in WooCommerce
  45. how to make members list directory through wordpress post custom meta key.
  46. Query posts without meta preload
  47. Adding multiple meta_key fields as orderby options to a WP Query via a function
  48. meta_value_num not ordering all items
  49. Query all posts if multiple meta key don’t exist
  50. meta_compare not comparing whole integer
  51. Does wp track views for posts?
  52. wp_query to print posts if have X custom field value
  53. Why is WP_Query not displaying expected data?
  54. WP_Query Meta_key is text value and need to sort as numeric not working
  55. filter wp_query result with custom field values
  56. Order post type by sum of two custom fields
  57. WP ForLoop to compare meta information of posts to determine what post to display
  58. How to order posts on each different category?
  59. Get every post with value in meta key
  60. Using get_posts to get posts based on a checkbox value with Advanced Custom Fields
  61. Retrieve select tag custom values from array and display them in current page with wp_query?
  62. Display all posts in a custom post type, grouped by a custom taxonomy. How to sort the posts alphabetically and the terms by ID?
  63. Order WP_Query results by meta key value in custom query
  64. WP Query and date format
  65. add_query_arg to compare and display events from a certain date
  66. Comparing 2 Decimal Numbers from custom fields and displaying posts
  67. Custom Query Fields – Altering Meta Value
  68. Query multiple post types, but different order for each
  69. How do i create a custom post query when the meta value is an array?
  70. How to add a “base” filter for all posts visible to visitors on the site?
  71. Sort custom post by custom field if none order by latest release
  72. Order (by ASC) posts with meta_key so posts without values are last
  73. how to include orderby value that is empty?
  74. WP Query – grouping posts by same meta key, adding together values from another key
  75. Retrieve all custom field values of a specific custom field metakey as an array, inside WP_Query
  76. meta_query is overriding default search
  77. Custom Woocommerce Product Query
  78. Comparing Meta Field date in WPQuery using Meta_Query?
  79. Move some posts to end of sort order, even if there is a sort in the wp_Query already
  80. Getting posts by custom field value
  81. Order posts by title and custom field value?
  82. sorting in wp query based on custom field value
  83. Sorting Posts by Taxonomy thats not within the query’s $args
  84. Order Posts By Custom Field That is an array of objects
  85. How to create URL parameters to run custom queries?
  86. List users by Year of Birth using a foreach loop to dynamically populate years and data
  87. Meta key in wp_query bug?
  88. Custom WP_query and integrating into theme file
  89. Query by Category and Custom Field – Shortcode
  90. Querying posts with meta key and meta value not returning anything
  91. WP_Query to show post from a category OR custom field – Not 100% answered
  92. Sorting Posts with meta value not working
  93. Order by three custom fields (Y-m-d), then separate into yearly sections
  94. Loop through multiple custom fields with increasing number
  95. new WP_query using custom fields
  96. Sorting: custom query with orderby meta_value_num THEN by title
  97. query post based on comparison
  98. Get posts by meta value except one post [closed]
  99. WP_Query sort by ACF date field (newest first) with blank dates first
  100. Sort by multiple columns using get_posts