Summary of mysql Date and Time Functions

The article has a large number of summary on mysql date and time usage. If you need it, please refer to this tutorial.

The article has a large number of summary on mysql date and time usage. If you need it, please refer to this tutorial.

The NOW () function obtains the current date and time:
Select now ();
/*
+ --------------------- +
| Now () |
+ --------------------- +
| 20:11:24 |
+ --------------------- +
1 row in set (0.02 sec)
*/Well, this function is frequently used. If you have nothing to do, check the current time. How long is it before work.
CURTIME () and CURDATE () are more specific than NOW (), and the current time and date are returned:
Select curtime (), curdate ();
/*
+ ----------- + ------------ +
| Curtime () | curdate () |
+ ----------- + ------------ +
| 20:13:28 |
+ ----------- + ------------ +
1 row in set (0.02 sec)
*/UNIX_TIMESTAMP () function returns UNIX:
Select unix_timestamp ();
/*
+ ------------------ +
| Unix_timestamp () |
+ ------------------ +
| 1, 1242648913 |
+ ------------------ +
1 row in set (0.00 sec)
*/Er, how can I convert a timestamp to a time? Is it necessary to use strtotime () of PHP ()? Oh, is that too much trouble? Come on.
The FROM_UNIXTIME () function converts a UNIX timestamp to a MySQL readable Date and Time:
Select from_unixtime (unix_timestamp ());
/*
+ --------------------------------- +
| From_unixtime (unix_timestamp () |
+ --------------------------------- +
| 20:17:23 |
+ --------------------------------- +
1 row in set (0.02 sec)
*/I think this example is easier to understand than reading a from_unixtime () directly, isn't it?
YEAR () -- YEAR, MONTH (), MONTHNAME () -- MONTH, DAYOFYEAR (), DAYOFWEEK (), DAYOFMONTH () -- the ordinal number of the day in YEAR, Week, and MONTH:
Select year (20030415012345), year );
/*
+ ---------------------- + ------------------ +
| Year (20030415012345) | year () |
+ ---------------------- + ------------------ +
| 1, 2003 | NULL |
+ ---------------------- + ------------------ +
1 row in set, 1 warning (0.02 sec)
*/
Select year (20030415012345), year ('1970-05-18 ');
/*
+ ---------------------- + -------------------- +
| Year (20030415012345) | year ('1970-05-18 ') |
+ ---------------------- + -------------------- +
| 2003/2009 |
+ ---------------------- + -------------------- +
1 row in set (0.02 sec)
*/See? If I do not put a date with a format in quotation marks, NULL is returned.
In addition, if year () does not contain a parameter, an error occurs. Well. I tried it just now.
Select month (20030414012345), month ('2017-05-18 '), month (), month ('2017-13-18 ');
/*
+ ----------------------- + --------------------- + ------------------- + --------------------- +
| Month (20030414012345) | month ('2017-05-18 ') | month ('2017-13-18') |
+ ----------------------- + --------------------- + ------------------- + --------------------- +
| 4 | 5 | NULL |
+ ----------------------- + --------------------- + ------------------- + --------------------- +
1 row in set, 2 warnings (0.09 sec)
*/See? Similarly, if the month is invalid, NULL is returned, and the formatted parameters must be enclosed in quotation marks.
Select monthname ('20170101'), monthname ('2017-05-18 '), monthname (), monthname ('2017-13-18 ');
/*
+ ----------------------- + ------------------------- +
| Monthname ('20170101') | monthname ('2017-05-18 ') | monthname ('2017-13-18') |
+ ----------------------- + ------------------------- +
| May | NULL |
+ ----------------------- + ------------------------- +
1 row in set, 2 warnings (0.02 sec)
*/I personally think this is the same as MONTH. The returned result is the name of the month, so it is in English.
Select dayofyear (20090518) as day1, dayofyear ('000000') as day2, dayofyear ('2017-05-18 ') as day3, dayofyear (20090518) as day4, dayofyear (2009) as day5;
/*
+ ------ +
| Day1 | day2 | day3 | day4 | day5 |
+ ------ +
| 138 | 138 | 138 | 138 | NULL |
+ ------ +
1 row in set, 1 warning (0.02 sec)
*/Er, DAYOFMONTH () and DAYOFWEEK () are the same, so there will be no more examples. Well.
In addition, like MONTHNAME (), DAYNAME () is used to obtain the name of the week (the day of the week) represented by the date )..
The WEEK () function returns the WEEK number of the year for the specified date. The YEERWEEK () function returns the WEEK of the year for the specified date:
Select week (20090301) as week1, week () as week2, week ('2017-03-01 ') as week3, yearweek (2009) as week4, yearweek () as week5, yearweek ('2017-05-18 ') as week6;
/*
+ ------- + -------- +
| Week1 | week2 | week3 | week4 | week5 | week6 |
+ ------- + -------- +
| 9 | NULL | 9 | 200920 | NULL | 200920 |
+ ------- + -------- +
1 row in set, 2 warnings (0.02 sec)
* The/HOUR (), MINUTE (), and SECOND () functions analyze the time values and return the hours, minutes, And seconds respectively:
Select hour (182300), second (123400), minute ('20: 56 ');
/*
+ -------------- + ---------------- + ----------------- +
| Hour (182300) | second (123400) | minute ('20: 56') |
+ -------------- + ---------------- + ----------------- +
| 18 | 0 | 56 |
+ -------------- + ---------------- + ----------------- +
1 row in set (0.00 sec)
*/
The values returned by WEEK () and YEARWEEK () are usually between 0 and 53 (I don't know how many weeks a year has). However,
We can change it to 1 to 54, and we can also choose the second parameter of the function to determine whether a week starts from Sunday or Monday.
TIME_TO_SEC () -- convert the time to seconds, SEC_TO_TIME () -- convert the number of seconds to a readable time:
Select sec_to_time (80), sec_to_time (3720), time_to_sec ('24: 01: 10 ');
/*
+ ----------------- + ------------------- + ------------------------- +
| Sec_to_time (80) | sec_to_time (3720) | time_to_sec ('24: 01: 10') |
+ ----------------- + ------------------- + ------------------------- +
| 00:01:20 | 01:02:00 | 86470 |
+ ----------------- + ------------------- + ------------------------- +
1 row in set (0.00 sec)
*/Date addition and subtraction are also commonly used. What are the ready-made methods provided by MySQL? Let me tell you.
DATE_ADD (), DATE_SUB () -- date addition and subtraction:
Select date_add ('2017-05-18 00:00:00 ', INTERVAL 6 MONTH );
/*
+ -------------------------------------------------- +
| Date_add ('2017-05-18 00:00:00 ', INTERVAL 6 MONTH) |
+ -------------------------------------------------- +
| 00:00:00 |
+ -------------------------------------------------- +
1 row in set (0.04 sec)
*/

Select date_add ('2017-05-18 00:00:00 ', interval '12' day_minute );
/*
+ ----------------------------------------------------------------- +
| Date_add ('2017-05-18 00:00:00 ', interval '12' day_minute) |
+ ----------------------------------------------------------------- +
| 03:45:00 |
+ ----------------------------------------------------------------- +
1 row in set (0.02 sec)
*/

Select date_sub ('2017-05-18 00:00:00 ', interval 6 hour );
/*
+ ------------------------------------------------- +
| Date_sub ('2017-05-18 00:00:00 ', interval 6 hour) |
+ ------------------------------------------------- +
| 18:00:00 |
+ ------------------------------------------------- +
1 row in set (0.00 sec)
*/

Select date_sub ('2017-05-18 00:00:00 ', interval '13-4' year_month );
/*
+ ------------------------------------------------------------- +
| Date_sub ('2017-05-18 00:00:00 ', interval '13-4' year_month) |
+ ------------------------------------------------------------- +
| 00:00:00 |
+ ------------------------------------------------------------- +
1 row in set (0.02 sec)
*/

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
Mysql> select DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
-> 5
Mysql> select WEEKDAY ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the day of the week by the English name)
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month of the date value (returned by English name)
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of the year for date.
Mysql> select QUARTER ('98-04-01 ');
-> 2
WEEK (date, first)
Returns the week number Of The Year for date. (the default value of first is 0. The value of first is 1, indicating that Monday is the start of the week. 0 starts from Sunday)
Mysql> select WEEK ('2017-02-20 ');
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8
YEAR (date)
Returns the year of date (range: 1000 to 9999)
Mysql> select YEAR ('98-02-03 ');
-> 1998
HOUR (time)
Returns the hour of time (ranging from 0 to 23)
Mysql> select HOUR ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)
Mysql> select SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM)
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for P1 and P2)
Mysql> select PERIOD_DIFF (9802,199703 );
-> 11
DATE_ADD (date, INTERVAL expr type)
DATE_SUB (date, INTERVAL expr type)
ADDDATE (date, INTERVAL expr type)
SUBDATE (date, INTERVAL expr type)