You can check it later.
I,MySQLGet current date and time
Now (): Obtain the current date + time (date + time) function:
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 10:26:23 |
+ --------------------- +
1 row in SET (0.00 Sec)
The same functions include current_timestamp () and localtime (), but now () is the easiest to remember. Therefore, we recommend that you use them.
Sysdate (): The datetime function is similar to now (). The difference is that now () is obtained at the execution start value, and sysdate () dynamically obtains the value during function execution.
You can see the following example:
Mysql> select now (), sleep (3), now ();
+ --------------------- + ---------- + --------------------- +
| Now () | sleep (3) | now () |
+ --------------------- + ---------- + --------------------- +
| 10:51:43 | 0 | 10:51:43 |
+ --------------------- + ---------- + --------------------- +
1 row in SET (3.02 Sec)
Mysql> select sysdate (), sleep (3), sysdate ();
+ --------------------- + ---------- + --------------------- +
| Sysdate () | sleep (3) | sysdate () |
+ --------------------- + ---------- + --------------------- +
| 10:52:09 | 0 | 10:52:12 |
+ --------------------- + ---------- + --------------------- +
1 row in SET (3.00 Sec)
As you can see, although sleep lasts for 3 seconds, the time values of the now () function are the same. The difference between the time values obtained by the sysdate () function is 3 seconds.
You can also set only the current date or time.
Curdate ():Get current date
Mysql> select curdate ();
+ ------------ +
| Curdate () |
+ ------------ +
| 2011-03-01 |
+ ------------ +
1 row in SET (0.01 Sec)
Curtime (): Get the current time function
Mysql> select curtime ();
+ ----------- +
| Curtime () |
+ ----------- +
| 10:55:12 |
+ ----------- +
1 row in SET (0.00 Sec)
II,MySQLExtract Date and Time Extract
With this function, we can extract the desired part from a time, such
Mysql> set @ Ct = '2017-03-01 11:16:14. 123 ';
Query OK, 0 rows affected (0.01 Sec)
Set the variable CT to a specific time value, accurate to subtle
Get its date value
Mysql> select date (@ CT );
+ ------------ +
| Date (@ CT) |
+ ------------ +
| 2011-03-01 |
+ ------------ +
1 row in SET (0.00 Sec)
View the quarter of the date
Mysql> select quarter (@ CT );
+ -------------- +
| Quarter (@ CT) |
+ -------------- +
| 1 |
+ -------------- +
1 row in SET (0.00 Sec)
View the week of the current year
Mysql> select Week (@ CT );
+ ----------- +
| Week (@ CT) |
+ ----------- +
| 9 |
+ ----------- +
1 row in SET (0.00 Sec)
In addition, year (), Day (), hour (), minute (), second () and so on are also described here.
Similar functions can be implemented using extract (). The syntax format is extract (year from @ CT ),
The disadvantage is that you need to press the keyboard several times.
DayofFunction:
Dayofweek (), dayofmonth (), and dayofyear () return the positions in the next week, January, and one year respectively.
Mysql> select dayofweek (@ CT );
+ ---------------- +
| Dayofweek (@ CT) |
+ ---------------- +
| 3 |
+ ---------------- +
1 row in SET (0.00 Sec)
Note: In fact, July 22, March 1 is Tuesday, but the number 3 is returned because it is counted from Sunday (1 = Sunday, 2 = Monday ,...)
Mysql> select dayofmonth (@ CT );
+ ----------------- +
| Dayofmonth (@ CT) |
+ ----------------- +
| 1 |
+ ----------------- +
1 row in SET (0.00 Sec)
Mysql> select dayofyear (@ CT );
+ ---------------- +
| Dayofyear (@ CT) |
+ ---------------- +
| 60 |
+ ---------------- +
1 row in SET (0.00 Sec)
Week () function
View the week of the current year
Mysql> select weekofyear (@ CT );
+ ----------------- +
| Weekofyear (@ CT) |
+ ----------------- +
| 9 |
+ ----------------- +
1 row in SET (0.00 Sec)
Returns the week and month names.
Dayname ()-The calculation date is the day of the week.
Mysql> select dayname (@ CT );
+ -------------- +
| Dayname (@ CT) |
+ -------------- +
| Tuesday |
+ -------------- +
1 row in SET (0.02 Sec)
Monthname ()-Date: January
Mysql> select monthname (@ CT );
+ ---------------- +
| Monthname (@ CT) |
+ ---------------- +
| March |
+ ---------------- +
1 row in SET (0.00 Sec)
Last_day (): Returns the last day of the month.
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 13:15:00 |
+ --------------------- +
1 row in SET (0.00 Sec)
Mysql> select last_day (now ());
+ ----------------- +
| Last_day (now () |
+ ----------------- +
| 2011-03-31 |
+ ----------------- +
1 row in SET (0.00 Sec)
This function calculates the number of days of the current month.
Mysql> select now (), Day (last_day (now ()));
+ --------------------- + ---------------------- +
| Now () | day (last_day (now () |
+ --------------------- + ---------------------- +
| 13:17:12 | 31 |
+ --------------------- + ---------------------- +
1 row in SET (0.00 Sec)
Iii. MySQL Date and Time Calculation Functions
Date_add (): Adds a time interval for the date.
The specific syntax is date_add (@ CT, interval num year/quarter/month/week/day/hour/minute/secont/microsecond );
Note: This function does not change the actual value of the variable @ CT.
Mysql> set @ Ct = now ();
Query OK, 0 rows affected (0.00 Sec)
Mysql> select @ CT;
+ --------------------- +
| @ CT |
+ --------------------- +
| 15:09:16 |
+ --------------------- +
1 row in SET (0.00 Sec)
Mysql> select date_add (@ CT, interval 1 day );
+ ------------------------------ +
| Date_add (@ CT, interval 1 day) |
+ ------------------------------ +
| 15:09:16 |
+ ------------------------------ +
1 row in SET (0.00 Sec)
Mysql> select @ CT;
+ --------------------- +
| @ CT |
+ --------------------- +
| 15:09:16 |
+ --------------------- +
1 row in SET (0.00 Sec)
Mysql> select date_add (@ CT, interval 1 week );
+ ------------------------------- +
| Date_add (@ CT, interval 1 week) |
+ ------------------------------- +
| 15:09:16 |
+ ------------------------------- +
1 row in SET (0.00 Sec)
Similar functions include adddate (), addtime (), and other functions. The corresponding function is date_sub (), which is called date subtraction.
Alternative date functions
Period_add (p, n): date plus/minus n months. The format of P should be yyyymm or yymm.
Period_diff (P1, P2): Date p1-p2, returns n months
Mysql> select period_add (), period_add (201103,-2 );
+ ---------------------- + ----------------------- +
| Period_add () | period_add (201103,-2) |
+ ---------------------- + ----------------------- +
| 201105/201101 |
+ ---------------------- + ----------------------- +
1 row in SET (0.00 Sec)
Mysql> select period_diff ('20140901', '20160901 ');
+ -------------------------------- +
| Period_diff ('20140901', '20160901') |
+ -------------------------------- +
| 2 |
+ -------------------------------- +
1 row in SET (0.00 Sec)
Date and time subtraction Function
Datediff (date1, date2): Two date1-date2 dates
Mysql> select datediff ('2017-03-09 ', '2017-03-01 ');
+ ------------------------------------- +
| Datediff ('2017-03-09 ', '2017-03-01') |
+ ------------------------------------- +
| 8 |
+ ------------------------------------- +
1 row in SET (0.00 Sec)
Mysql> select datediff ('2017-03-01 ', '2017-03-09 ');
+ ------------------------------------- +
| Datediff ('2017-03-01 ', '2017-03-09') |
+ ------------------------------------- +
|-8 |
+ ------------------------------------- +
1 row in SET (0.00 Sec)
Timediff (time1, time2): Two time subtraction
Mysql> select timediff ('2017-03-03 15:33:00 ', '2017-03-02 15:33:59 ');
+ --------------------------------------------------------- +
| Timediff ('2017-03-03 15:33:00 ', '2017-03-02 15:33:59') |
+ --------------------------------------------------------- +
| 23:59:01 |
+ --------------------------------------------------------- +
1 row in SET (0.00 Sec)
Mysql> select timediff ('15: 33: 00', '15: 33: 59 ');
+ --------------------------------- +
| Timediff ('15: 33: 00', '15: 33: 59') |
+ --------------------------------- +
|-00:00:59 |
+ --------------------------------- +
1 row in SET (0.00 Sec)
Iv. MySQL Date and Time conversion functions
Time_to_sec (time): Time-> Second Conversion Function
Sec_to_time (Num): Second --> time Conversion Function
Mysql> select time_to_sec ('01: 00: 00 ');
+ ------------------------- +
| Time_to_sec ('01: 00: 00') |
+ ------------------------- +
| 1, 3600 |
+ ------------------------- +
1 row in SET (0.00 Sec)
Mysql> select sec_to_time (3600 );
+ ------------------- +
| Sec_to_time (3600) |
+ ------------------- +
| 01:00:00 |
+ ------------------- +
1 row in SET (0.00 Sec)
To_days (date): Date --> the start date of the day conversion function is 0000-00-00.
From_days (Num): Day --> converts a number to a specific date.
Mysql> select to_days ('2017-03-01 ');
+ ----------------------- +
| To_days ('2017-03-01 ') |
+ ----------------------- +
| 1, 734562 |
+ ----------------------- +
1 row in SET (0.00 Sec)
Mysql> select from_days (734562 );
+ ------------------- +
| From_days (734562) |
+ ------------------- +
| 2011-03-01 |
+ ------------------- +
1 row in SET (0.00 Sec)
Str_to_date (STR, date): String --> date Conversion Function
It can convert disordered characters into date formats.
Mysql> select str_to_date ('01. 03.2011 ',' % m. % d. % y ');
+ --------------------------------------- +
| Str_to_date ('01. 03.2011 ',' % m. % d. % y') |
+ --------------------------------------- +
| 2011-01-03 |
+ --------------------------------------- +
1 row in SET (0.00 Sec)
Mysql> select str_to_date ('2014/1/123', '% m/% d/% y ');
+ --------------------------------------- +
| Str_to_date ('2014/1/123', '% m/% d/% y') |
+ --------------------------------------- +
| 2011-01-03 |
+ --------------------------------------- +
1 row in SET (0.00 Sec)
Exercises:
Taking the table centralmobile_logs as an example, the table currently has a total of more than 2.7 million data records
Mysql> select count (*) from centralmobile_logs;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2725403 |
+ ---------- +
1 row in SET (0.00 Sec)
Make some statistics on it now
Query the total amount of data in the past 30 days
Mysql> select count (*) from centralmobile_logs where to_days (curdate ()-to_days (create_time) <= 30;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2367518 |
+ ---------- +
1 row in SET (3.38 Sec)
Mysql> select count (*) from centralmobile_logs where datediff (curdate (), create_time) <= 30;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2367518 |
+ ---------- +
1 row in SET (3.29 Sec)
View data on the first day of each month
Mysql> select count (*) from centralmobile_logs where dayofmonth (create_time) = 1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 161293 |
+ ---------- +
1 row in SET (3.14 Sec)
View data before January 11, January 31
Mysql> select count (*) from centralmobile_logs where create_time <= '2017-01-31 00:00:00 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 413797 |
+ ---------- +
1 row in SET (0.17 Sec)
View data for February years
Mysql> select count (*) from centralmobile_logs where monthname (create_time) = 'february 'and year (create_time) = 2011;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2149284 |
+ ---------- +
1 row in SET (3.94 Sec)
View the accumulated data of every Sunday in 11 years
Mysql> select count (*) from centralmobile_logs where dayname (create_time) = 'sunday' and year (create_time) = 2011;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 479033 |
+ ---------- +
1 row in SET (3.88 Sec)
View the total data inserted at every day
Mysql> select count (*) from centralmobile_logs where time (create_time) = '00: 00: 00 ';
+ ---------- +
| Count (*) |
+ ---------- +
| 37 |
+ ---------- +
1 row in SET (3.99 Sec)