Sorting data according to current date

Question

I have a table of posts like this

+--------------------+--------------+
| Field              | Type         |
+--------------------+--------------+
| id                 | int(11)      |
| title              | varchar(255) |
| body               | text         | 
| published_at       | datetime     |
+--------------------+--------------+

What I want achieve is to order by published_at. Normally I would do:

SELECT * FROM posts ORDER BY published_at;

But my requirement here is that the query should fetch the results from current date on top and then the previous ones and after that fetch those from future.

Current my results are as follows:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
+-------------------------------+----+---------------------+

Whereas my desired outcome would be:

+-------------------------------+----+---------------------+
| title                         | id | published_at        |
+----------------------------------------------------------|
| 20+ Tools For RoR Development |  2 | 2015-08-25 12:21:23 |
| Hello world                   |  1 | 2015-01-06 12:21:16 |
| Ruby on Rails best practices  |  5 | 2015-01-21 00:00:00 |
| Visual search                 |  4 | 2015-03-12 12:27:26 |
| Angular JS tutorial           |  3 | 2015-09-31 10:51:55 |
+-------------------------------+----+---------------------+

I managed this using aggregate functions in mongo. – Vikram Apr 20 '21 at 13:27 — Vikram, Apr 20 '21 at 13:27

score 9 · Accepted Answer · edited Aug 25 '15 at 10:32

9

Solution without using UNION/CASE

SELECT * FROM posts
ORDER BY
    DATE(published_at)=DATE(NOW()) DESC,
    DATE(published_at)<DATE(NOW()) DESC,
    DATE(published_at)>DATE(NOW()) ASC`

check if this works. Its working fine with stimulated data. You can change desc to asc according to your sorting requirement for past and future dates

edited Aug 25 '15 at 10:32

jkavalik

1,296
11
21

answered Aug 25 '15 at 10:24

Excel

269
2
13

Nice one. You might want to add one more order by `published_at` to sort data in each "group" the way you want. – jkavalik Aug 25 '15 at 10:35
How we can achieve this for MongoDB with spring data? – Vikram Apr 14 '21 at 12:30
I managed this using aggregate functions in mongo. Thanks for the logic to fetch the record from DB. – Vikram Apr 20 '21 at 13:28

score 4 · Answer 2 · answered Aug 25 '15 at 10:24

4

There is a possible solution without UNION (ALL):

select
 id, title, published_at,
 case
   when date(published_at) = curdate() then '1-now'
   when date(published_at) < curdate() then '2-past'
   else '3-future'
   end as order_group
from t
order by order_group asc, published_at asc;

http://sqlfiddle.com/#!9/59c48/2

It needs a filesort to run so may be not as fast as UNION ALL for bigger data. UNION has to remove duplicates so I expect it to be similar.

answered Aug 25 '15 at 10:24

jkavalik

1,296
11
21

This is better it follow the rule of nonduplicating code. The "union" answers contain more than one select and this aswer contain the business rule in one location. – Florin Ghita Aug 25 '15 at 10:36

score 2 · Answer 3 · answered Aug 25 '15 at 10:13

2

Give this a try:

SELECT * FROM posts WHERE published_at = CONVERT(DATE, GETDATE())
UNION
SELECT * FROM posts WHERE published_at != CONVERT(DATE, GETDATE()) ORDER BY published_at;

answered Aug 25 '15 at 10:13

Hisham Maudarbocus

610
8
18

or you might add union all, it does not matter cause the two quries return different values – Hisham Maudarbocus Aug 25 '15 at 10:23
It matters because: 1. Union eliminate duplicates. 2. As a consequence of first point, union is less faster because it tries to eliminate duplicates even there are no duplicates. It does more work. – Florin Ghita Aug 25 '15 at 10:31

Matt · Answer 4 · 2015-08-25T11:18:09.863

2

Using a different approach without the need for Unions.

SELECT *
FROM(
    SELECT 1 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) = CURDATE()
    UNION ALL
    SELECT 2 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) < CURDATE()  
    UNION ALL
    SELECT 3 AS Rank, title, id, published_at FROM posts WHERE DATE(published_at) > CURDATE()
) a
ORDER BY rank, published_at

OUTPUT:

Rank    title                           id  published_at
1       20+ Tools For RoR Development   2   August, 25 2015 12:21:23
2       Hello world                     1   January, 06 2015 12:21:16
2       Ruby on Rails best practices    5   January, 21 2015 00:00:00
2       Visual search                   4   March, 12 2015 12:27:26
3       Angular JS tutorial             3   October, 01 2015 10:51:55

SQL Fiddle: http://sqlfiddle.com/#!9/f6c1b/9/0

edited Aug 25 '15 at 11:18

answered Aug 25 '15 at 10:16

Matt

14,906
27
99
149

1

"But my requirement here is that the query should fetch the results from current date on top and **then** the previous ones and **after that** fetch those from future." – Florin Ghita Aug 25 '15 at 10:17
@Matt sorry, My previous comment is useless because ordering by published_at will order the previous entries before those from future. – Florin Ghita Aug 25 '15 at 10:39
Can we clean up the comments for future readers please, i will delete mine – Matt Aug 25 '15 at 10:53
2

@Matt you should order by rank, then by published_at. – Florin Ghita Aug 25 '15 at 11:07
@FlorinGhita Yup that got it! :) – Matt Aug 25 '15 at 11:18

score 0 · Answer 5 · answered Aug 25 '15 at 10:19

SELECT * 
FROM posts 
WHERE date(published_at) = CURDATE()

UNION ALL

SELECT * 
FROM posts 
WHERE date(published_at) != CURDATE() 
ORDER BY published_at;

CURDATE() will give today's date without time
date(published_at) will trim the time from published_at

score 0 · Answer 6 · answered Apr 20 '21 at 13:41

Without Union, we can get the result using the below query

   SELECT * 
     CASE
       WHEN DATEDIFF(day, published_at, curdate()) > 0 then '0'
       WHEN DATEDIFF(day, published_at, curdate()) < 0 then 
        DATEDIFF(day, published_at, curdate())
       END AS dayInterval
    FROM posts
    order by dayInterval desc, published_at asc;

Sorting data according to current date

6 Answers6

Linked