) MySQL date and time function 1

MySQL Date and Time Functions (1)
Author: mr. Zhang
MySQL Date and Time Style
There are many useful date and time functions in MySQL. We often see that a lot of calculations that are applied to date are in the code
But they can actually use the built-in functions in MySQL. In our actual view of MySQL
Before the time and date functions, let's take a look at the storage content of MySQL.
Datetime YYYY-MM-DD hh: mm: Ss.
Date YYYY-MM-DD
Timestamp yyyymmddhhssmm
Time hh: mm: SS
Year YYYY

The timestamp column stores all 14 characters, but you can define its display mode by yourself. For example, if
According to the timestamp (2) method, only the two-digit year is displayed, but all data is still
Saved. If you want to list all the data in the future, you only need to change the conditions, and all the content will be displayed.
. The following table shows different definitions and results.

Timestamp (14) yyyymmddhhmmss
Timestamp (12) yymmddhhmmss
Timestamp (10) yymmddhhmm
Timestamp (8) yyyymmdd
Timestamp (6) yymmdd
Timestamp (4) yymm
Timestamp (2) YY

MySQL is very tolerant of the format of the date to be read. Although MySQL has a wide range of conventions, you
You can still use the method you like. For example, if you create a table as follows:

Create Table time_table (DT datetime );
You can use the following Conventions to insert a record:
Insert into time_table (DT) values ('2017-03-31 11:22:12 ');
You can also use '+' and '=' as follows:
Insert into time_table (DT) values ('2017 = 03 = 31 11 + 22 + 12 ');
Of course, although this method is feasible, we recommend that you use the agreed method if it is not necessary.
Simple date calculation
Date calculation is very simple. The first function to be viewed is year (). For example:
Mysql> Select Year ('2014-03-31 ');
+ -------------------- +
| Year ('1970-03-31 ') |
+ -------------------- +
| 1, 2003 |
+ -------------------- +
We can use the "+" and "-" operators to directly perform some simple operations. For example, if you want to see which year the date is five years later,
You can use:
Mysql> Select Year ('2014-03-31 ') + 5;
+ ---------------------- +
| Year ('1970-03-31 ') + 5 |
+ ---------------------- +
| 1, 2008 |
+ ---------------------- +
Similarly, the date five years ago:
Mysql> Select Year ('2014-03-31 ')-5;
+ ---------------------- +
| Year ('1970-03-31 ')-5 |
+ ---------------------- +
| 1, 1998 |
+ ---------------------- +
Of course, if you do not know the specific time, MySQL provides you with the now () function:
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 00:32:21 |
+ --------------------- +
Or current_date () function is only used to provide year, month, and day:
Mysql> select current_date ();
+ ---------------- +
| Current_date () |
+ ---------------- +
| 2003-03-31 |
+ ---------------- +
Of course, there are other time interval value functions: Month (), dayofmonth (), hour (), minute () and
Second (). For example:

Mysql> select month (now () as m,
Dayofmonth (now () as D,
Hour (now () as H,
Minute (now () as m,
Second (now () as S;
+ ------ +
| M | d | H | M | S |
+ ------ +
| 3 | 31 | 1 | 53 | 38 |
+ ------ +

Dayofmonth () function is an exception to naming conventions, because he uses other methods to return dates.
Dayofmonth () returns the number of days from 1 to 31, and dayname () returns the actual day, dayofweek ()
Returns 1 (Sunday) to 7 (Saturday), and dayofyear () returns 1 to 366.
Some examples:
Mysql> select dayname ('2017-01-01 ');
+ ----------------------- +
| Dayname ('2017-01-01 ') |
+ ----------------------- +
| Saturday |
+ ----------------------- +
Mysql> select dayofweek ('2017-01-01 ');
+ ------------------------- +
| Dayofweek ('2017-01-01 ') |
+ ------------------------- +
| 7 |
+ ------------------------- +
Mysql> select dayofyear ('2017-12-31 ');
+ ------------------------- +
| Dayofyear ('2017-12-31 ') |
+ ------------------------- +
| 1, 366 |
+ ------------------------- +
Age Calculation
The most common date calculation is age calculation. Unfortunately, there is no function to directly implement this function.
Now let's do some simple mathematical calculations-you will find that I am starting from the current time (31
March 2003 ). First, we simply remove the date of birth from the current date:
Mysql> Select Year (current_date)-year ('2017-01-01 ');
+ --------------------------------------- +
| Year (current_date)-year ('1970-01-01 ') |
+ --------------------------------------- +
| 32 |
+ --------------------------------------- +
If you reply directly to this number, it is obvious that the calculation is correct. A person born in January 1, 1971
March 31, 2003 is 32 years old. But what if the person was born in December 31:
Mysql> Select Year (current_date)-year ('2017-12-31 ');
+ --------------------------------------- +
| Year (current_date)-year ('1970-12-31 ') |
+ --------------------------------------- +
| 32 |
+ --------------------------------------- +
Age error. This person is not 32 years old. The problem is that only the year is calculated, and only the year is used for subtraction,
Ignore the month and date. The best comparison method is to compare the date with the month to see if the date of birth is earlier than the current day.
If so, you only need to calculate the year; otherwise, you need to subtract a portion of the year. This may sound tricky,
Actually not. Let's take a moment to understand that MySQL uses 1 for true. False
For example:
Mysql> select 23> 19;
+ ------- +
| 23> 19 |
+ ------- +
| 1 |
+ ------- +
Mysql> select 23 <19;
+ ------- +
| 23 <19 |
+ ------- +
| 0 |
+ ------- +
23> 19 (true), so MySQL returns '1', and 23 is not less than 19 (false), so MySQL returns '0 '. Now we
A method is required to return the 'Mm-dd' date format. Fortunately, we have a function that can be directly used,
Right (), which is used to return the number of digits defined from the right.

For example:
Mysql> select right ('abcdef', 2 );
+ ------------------- +
| Right ('abcdef', 2) |
+ ------------------- +
| EF |
+ ------------------- +
In this way, the right-start number and two-digit value 'ef 'are returned '. Of course, if the number of bits you requested exceeds the number of BITs recorded
All content:
Mysql> select right ('abcdef', 9 );
+ ------------------- +
| Right ('abcdef', 9) |
+ ------------------- +
| Abcdef |
+ ------------------- +
To return the 'Mm-dd' style, you need to return 5 digits of data, for example:
Mysql> select right (current_date (), 5 );
+ ------------------------- +
| Right (current_date (), 5) |
+ ------------------------- +
| 03-31 |
+ ------------------------- +
Now we have all the data of an age. We can use the following method to calculate it:
Mysql> Select Year (current_date ()-year ('2017-12-31 ')
-(Right (current_date (), 5) <'12-31 ') as age;
+ ------ +
| Age |
+ ------ +
| 31 |
+ ------ +
Now let's look at some less common functions. First, let's take a look at the acceptable definitions of MySQL for these functions.
Data type.
Second SS
Minute mm
Hour HH
Day dd
Month mm
Year YY
Minute_second mm: SS
Hour_minute hh: mm
Day_hour DD: hh
YYYY-MM year_month
Hour_second hh: SS
Day_minute dd hh: mm
Day_second dd hh: mm: SS
As you can see. In fact, MySQL still has many things to do before you call the program.
Use date_add () and date_sub () to increase or decrease the time and date
Date_add () function and adddate () function share the same role. They are used to add a specific date segment or time segment on a given date and time. date_add (date time, time Interval represented by date-time data
Interval ).
For example, you can use:
Mysql> select date_add ('2017-07-13 ', interval 14 day );
+ ----------------------------------------- +
| Date_add ('2017-07-13 ', interval 14 day) |
+ ----------------------------------------- +
| 2003-07-27 |
+ ----------------------------------------- +