Mysql function usage record (1) -- DATEDIFF, CONCAT, datediffconcat

Mysql function usage record (1) -- DATEDIFF, CONCAT, datediffconcat

So far, I have not used Mysql functions in a unified way, and I have used them all before learning. Recently, I began to learn about Linux, so in order to prevent the Mysql functions encountered in this phase from being forgotten after the end of this period, I began to make a simple record here.

My friend asked me to help me read an SQL statement before leaving work yesterday. It is about birthday reminders. in actual application, we need to remind users of their birthdays one day in advance, SQL is used to query users who will have their birthdays in the next day. The CONCAT and DATEDIFF functions are used in her original SQL statements. I have never used them before. Therefore, I will record the usage and ideas here after solving the problem.

First, let's look at the definition and usage of the CONCAT and DATEDIFF functions:

CONCAT (): used to connect two or more strings. If any parameter is null, the return value is null.

DATEDIFF (): returns the number of days between two dates.

Next, let's look at the demand:

Query the users who have their birthdays on the next day.

After clarifying the requirements and function usage, let's look at the ideas:

1. Since we want to check the users who will have their birthdays on the next day, we can simply think of it as a query of users whose birthdate is one day later than the current date, in this case, only the users born one day after the current year, current month, and current date can be queried. Obviously, this is not the case. Let's change the mindset. Assume that all users are born in the same year, in this way, we will not be able to obtain users whose birthdate is one day later than the current date ~

2. We can see in 1 that it seems that there is no problem, but think carefully, if the current date is December 31, then the next day (that is, January 1) can users who celebrate their birthdays be able to query data based on the 1 idea? Obviously not. Let's look at the year December 31 and year January 1. Do you think of a new year? Yes, it's just a new year. In this case, we replace the year of the birthday of all users with the year of the current year, in this way, if the number of days in a day is 1, can you screen out users who have their birthdays on the day after January 1, December 31? ^_^ ~

Finally, let's look at the implementation of SQL:

1. Replacement + combined birthday date:

Replace the current year with the birthday date:

CONCAT (YEAR (NOW (), DATE_FORMAT (birthday, '-% m-% D '))

Replace the year of the current year with the year after the New Year:

CONCAT (YEAR (NOW () + 1, DATE_FORMAT (birthday, '-% m-% D '))

2. After processing, the birthday date and the number of days in the current day are as follows:

Normal:

DATEDIFF (CONCAT (YEAR (NOW (), DATE_FORMAT (birthday, '-% m-% D'), NOW ())

New Year:

DATEDIFF (CONCAT (YEAR (NOW () + 1, DATE_FORMAT (birthday, '-% m-% D'), NOW ())

3. Integrated Query:

　　

SELECT *FROM userWHERE    DATEDIFF(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d')),NOW()) = 1OR    DATEDIFF(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d')),NOW()) = 1

Finally:

Two Parameters in the DATEDIFF function. The value of the first parameter is greater than the last parameter. The value is greater than or equal to 0, and the value of the previous parameter is less than or equal to 0.

Record complete!