How should SQL paging query data be sorted from nearest to far according to birthday date?

There is currently a user data table user_table, which contains a birthday field to store the timestamp data corresponding to the user's birth date (such as 1994-01-01, 1996-07-1)

Now we need to search user data in pages according to the order of birthdays from recent to far
For example, if today 2016-07-28, the birthday dates are
1995-07-30, 1993-11-31, The four user data of 1997-08-29, 1995-07-27
are sorted into
1995-07-30, 1997-08-29, 1993-11-31, 1995 -07-27

How to write a SQL statement to satisfy such query conditions?
Or what should be the appropriate modification to the structure of my data table that stores birthdays?

Reply content:

There is currently a user data table user_table, which contains a birthday field to store the timestamp data corresponding to the user's birth date (such as 1994-01-01, 1996-07-1)

Now we need to search user data in pages according to the order of birthdays from recent to far
For example, if today 2016-07-28, the birthday dates are
1995-07-30, 1993-11-31, The four user data of 1997-08-29, 1995-07-27
are sorted into
1995-07-30, 1997-08-29, 1993-11-31, 1995 -07-27

How to write a SQL statement to satisfy such query conditions?
Or what should be the appropriate modification to the structure of my data table that stores birthdays?

Simple approach without considering data volume and performance issues:

1. Add a field to save the information of dayofyear. For example, the field name is day_of_year, then your insertion statement is like this:

<code class="mysql">insert into user_table set ... = ..., day_of_year = DAYOFYEAR('1995-07-30');</code>

1. Query twice when querying, first query if the day_of_year field is greater than your current value, then query if this field is less than the current value, sort by day_of_year:

<code class="mysql">select * from user_table where day_of_year >= DAYOFYEAR(CURDATE()) order by day_of_year asc;
select * from user_table where day_of_year < DAYOFYEAR(CURDATE()) order by day_of_year asc;</code>

Look at one less condition before
mysql converts the time 1995-07-30 into the day of the year DAYOFYEAR('1995-07-30'), subtracts the day of the year today, and gets a Just take the difference and order by.

If the birthdays are on the same month and day, you can sort them in ascending order by year.

<code>select * from user_table order by (DAYOFYEAR(birthday)-DAYOFYEAR(curdate())),year(birthday) ASC</code>

Idea: What you are actually doing is sorting by month after subtraction, and then sorting by day after subtraction

<code>mysql> SELECT name, birthday FROM user_table
 -> WHERE MONTH(birthday) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
 -> ORDER BY DAY(birthday) asc;
 </code>

The above code can get the birthday data one month after the current month and arrange it in ascending day order. You can just write a repeat to take out the latter ones

<code>DROP TEMPORARY TABLE IF EXISTS aa;
DROP TEMPORARY TABLE IF EXISTS bb;
create temporary table aa(select birthday from user_table 
WHERE substring(birthday,6,10)>'07-28' ORDER BY substring(birthday,6,10) asc);
create temporary table bb(select birthday from user_table 
WHERE substring(birthday,6,10)<'07-28' ORDER BY substring(birthday,6,10) asc );
SELECT * FROM aa Union All SELECT * FROM bb
测试了一下，效果如图所示</code>

1995-07-30
1997-08-29
1993-11-31
1999-01-01
1995-07-27