How many days is there before mysql computing's birthday (after this year, it will be calculated as per the year) _ MySQL

Mysql calculates the number of days before the birthday (after this year, it will be calculated as per the year) bitsCN.com

How many days does mysql computing have before its birthday (after this year, it will be calculated as per the year)

Table 1:

Name birthday

Aaaa

Bbbb 2013-01-05 34

SQL:

Java code

SELECT a. * FROM

(SELECT t. name, t. birthday, case when t. B> 0 THEN t. B ELSE t. a END days FROM (

Select name, birthday, DATEDIFF (CONCAT (DATE_FORMAT (NOW (), '% Y') + 1, DATE_FORMAT (birthday,'-% m-% d ')), NOW (),

DATEDIFF (CONCAT (DATE_FORMAT (NOW (), '% Y'), DATE_FORMAT (birthday,'-% m-% d'), NOW () B

FROM hy_member) t) a WHERE a. days BETWEEN 0 AND 40;

Mysql date functions

1. MySQL adds a time interval for the date: date_add ()

Set @ dt = now ();

Select date_add (@ dt, interval 1 day);-add 1 day

Select date_add (@ dt, interval 1 hour);-add 1 hour

Select date_add (@ dt, interval 1 minute);-add 1 minute

Select date_add (@ dt, interval 1 second);-add 1 second

Select date_add (@ dt, interval 1 microsecond);-add 1 millisecond

Select date_add (@ dt, interval 1 week);-add 1 week

Select date_add (@ dt, interval 1 month);-add January 1, January

Select date_add (@ dt, interval 1 quarter);-add Season 1

Select date_add (@ dt, interval 1 year);-plus 1 year

MySQL adddate () and addtime () functions can be replaced by date_add. The following is an example of addtime () implemented by date_add:

Mysql> set @ dt = '2017-09-09 12:12:33 ';

Mysql>

Mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second);-1 hour 15 minutes 30 seconds

Date_add (@ dt, interval '01: 15: 30 'hour_second)

Result: 13:28:03

Mysql> select date_add (@ dt, interval '1 01:15:30 'day_second);-1 day, 1 hour, 15 minutes, 30 seconds

Date_add (@ dt, interval '1 01:15:30 'day_second)

2008-08-10 13:28:03

The date_add () function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @ dt respectively"

2. MySQL is a date minus a time interval: date_sub ()

Mysql> select date_sub ('2017-01-01 00:00:00 ', interval '1' day_second );

Date_sub ('2017-01-01 00:00:00 ', interval '1' day_second)

The usage of MySQL date_sub () DATETIME function is the same as that of date_add. In addition, there are two subdate () and subtime () functions in MySQL. we recommend that you use date_sub () instead.

3. MySQL alternative date functions: period_add (P, N), period_diff (P1, P2)

The format of the function parameter "P" is "YYYYMM" or "YYMM". The second parameter "N" indicates adding or subtracting N month ).

MySQL period_add (P, N): date plus/minus N months.

Mysql> select period_add (20080808), period_add (,-2)

| Period_add (20080808) | period_add (,-2) |

Result | 200810 | 20080806 |

MySQL period_diff (P1, P2): date P1-P2, returns N months.

Mysql> select period_diff (200808,200 );

Period_diff (200808,200)

Result: 7

In MySQL, these two date functions are rarely used.

4. MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2)

MySQL datediff (date1, date2): two dates subtract date1 date2 and return the number of days. Website production Learning Network

Select datediff ('2017-08-08 ', '2017-08-01');-7

Select datediff ('2017-08-01 ', '2017-08-08');-7

MySQL timediff (time1, time2): two dates minus time1 time2, return the time difference.

Select timediff ('2017-08-08 08:08:08 ', '2017-08-08 00:00:00');-08:08:08

Select timediff ('08: 08: 08', '00: 00: 00');-08:08:08

Note: the two parameter types of the timediff (time1, time2) function must be the same.

BitsCN.com