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

Source: Internet
Author: User
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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.