MySQL date and time function summary

The now () function gets the current date and time:
Select Now ();
/*
+---------------------+
| Now () |
+---------------------+
| 2009-05-18 20:11:24 |
+---------------------+
1 row in Set (0.02 sec)
* * Ah, this function is often used by me, nothing just look at the current time. See how long it is before work, hehe.
Curtime () and curdate () are more specific than now (), returning the current time and date:
Select Curtime (), curdate ();
/*
+-----------+------------+
| Curtime () | Curdate () |
+-----------+------------+
| 20:13:28 | 2009-05-18 |
+-----------+------------+
1 row in Set (0.02 sec)
The */unix_timestamp () function returns the UNIX timestamp:
Select Unix_timestamp ();
/*
+------------------+
| Unix_timestamp () |
+------------------+
| 1242648913 |
+------------------+
1 row in Set (0.00 sec)
/er, how do I convert time stamps to time? Are you sure you want to use PHP's Strtotime ()? Oh, that's too much trouble, huh? Don't worry, it's coming.
The From_unixtime () function converts the Unix timestamp to the date and time when MySQL is readable:
Select From_unixtime (Unix_timestamp ());
/*
+---------------------------------+
| From_unixtime (Unix_timestamp ()) |
+---------------------------------+
| 2009-05-18 20:17:23 |
+---------------------------------+
1 row in Set (0.02 sec)
* * I feel that this example is easier to understand than reading a from_unixtime directly (), isn't it?
Year ()--years, MONTH (), MonthName ()--month, DayOfYear (), DayOfWeek (), DayOfMonth ()--the ordinal number of the day in the year, Week, and month:
Select year (20030415012345), year (2009-05-18);
/*
+----------------------+------------------+
| Year (20030415012345) | Year (2009-05-18) |
+----------------------+------------------+
|             2003 | NULL |
+----------------------+------------------+
1 row in Set, 1 Warning (0.02 sec)
*/
Select year (20030415012345), year (' 2009-05-18 ');
/*
+----------------------+--------------------+
| Year (20030415012345) | Year (' 2009-05-18 ') |
+----------------------+--------------------+
|               2003 | 2009 |
+----------------------+--------------------+
1 row in Set (0.02 sec)
* * See? If I don't enclose the formatted date in quotes, the return is null.
Also, if year () has no parameters, there will be an error. Well. I've just had a trial.
Select Month (20030414012345), month (' 2009-05-18 '), month (2009-05-18), month (' 2009-13-18 ');
/*
+-----------------------+---------------------+-------------------+---------------------+
| Month (20030414012345) | Month (' 2009-05-18 ') | Month (2009-05-18) | Month (' 2009-13-18 ') |
+-----------------------+---------------------+-------------------+---------------------+
|                   4 |              5 |                NULL | NULL |
+-----------------------+---------------------+-------------------+---------------------+
1 row in Set, 2 warnings (0.09 sec)
* * See? Similarly, if the month is not legal, it will return null, HA, and the formatted parameter must be enclosed in quotation marks.
Select MonthName (' 20090518 '), MonthName (' 2009-05-18 '), MonthName (2009-05-18), MonthName (' 2009-13-18 ');
/*
+-----------------------+-------------------------+-----------------------+-------------------------+
| MonthName (' 20090518 ') | MonthName (' 2009-05-18 ') | MonthName (2009-05-18) | MonthName (' 2009-13-18 ') |
+-----------------------+-------------------------+-----------------------+-------------------------+
| May | May | NULL | NULL |
+-----------------------+-------------------------+-----------------------+-------------------------+
1 row in Set, 2 warnings (0.02 sec)
I personally feel the same as month () ha. Just return the result is the name of the month, so of course it is in English.
Select DayOfYear (20090518) as Day1,dayofyear (' 20090518 ') as Day2,dayofyear (' 2009-05-18 ') as Day3,dayofyear ( 20090518203000) as Day4,dayofyear (20091318) as Day5;
/*
+------+------+------+------+------+
| Day1 | Day2 | Day3 | Day4 | Day5 |
+------+------+------+------+------+
|  138 |  138 |  138 | 138 | NULL |
+------+------+------+------+------+
1 row in Set, 1 Warning (0.02 sec)
* Well, dayofmonth () and DayOfWeek () like this, no more examples. Well.
At the same time, as with MonthName (), there are also dayname () to obtain the name of the day (that is, days of the week) represented by that date.
The WEEK () function returns the week ordinal of a year for the specified date, and the Yeerweek () function returns which week of the year the specified date is:
Select Week (20090301) as Week1,week (2009-03-01) as Week2,week (' 2009-03-01 ') as Week3,yearweek (20090518) as Week4, Yearweek (2009-05-18) as Week5,yearweek (' 2009-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)
*/hour (), MINUTE (), and second () functions parse time values and return the parts of 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 (the number of weeks in a year is not clear), but
We can modify it to between 1 and 54, and we can also decide whether the one week starts from Sunday or Monday by selecting the second parameter of the function.
Time_to_sec ()--turn time into seconds, sec_to_time ()--convert seconds to an easy read time:
Select Sec_to_time, 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 is what we often use, so what does MySQL give us the ready-made method? Let me tell you.
Date_add (), Date_sub ()--date addition minus:
Select Date_add (' 2009-05-18 00:00:00 ', INTERVAL 6 MONTH);
/*
+--------------------------------------------------+
| Date_add (' 2009-05-18 00:00:00 ', INTERVAL 6 MONTH) |
+--------------------------------------------------+
| 2009-11-18 00:00:00 |
+--------------------------------------------------+
1 row in Set (0.04 sec)
*/

Select Date_add (' 2009-05-18 00:00:00 ', Interval ' 03:45 ' Day_minute);
/*
+-----------------------------------------------------------------+
| date_add (' 2009-05-18 00:00:00 ', Interval ' 03:45 ' Day_minute) |
+-----------------------------------------------------------------+
| 2009-05-30 03:45:00                                                |
+-----------------------------------------------------------------+
1 row in Set (0.02 sec)
*/

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

Select Date_sub (' 2009-05-18 00:00:00 ', Interval ' 13-4 ' year_month);
/*
+-------------------------------------------------------------+
| Date_sub (' 2009-05-18 00:00:00 ', Interval ' 13-4 ' Year_month) |
+-------------------------------------------------------------+
| 1996-01-18 00:00:00 |
+-------------------------------------------------------------+
1 row in Set (0.02 sec)
*/

DayOfWeek (date)
Return date is the day of the week (1= Sunday, 2 = Monday,...... 7= Saturday, ODBC Standard)
Mysql> Select DayOfWeek (' 1998-02-03 ');
-> 3
Weekday (date)
Return date is the day of the week (0= Monday, 1 = Tuesday,...... 6= Sunday).
Mysql> Select Weekday (' 1997-10-04 22:23:00 ');
-> 5
Mysql> Select Weekday (' 1997-11-05 ');
-> 2
DayOfMonth (date)
Returns date is the day ordinal of January (within 1 to 31)
Mysql> Select DayOfMonth (' 1998-02-03 ');
-> 3
DayOfYear (date)
Return date is the day ordinal of the year (within 1 to 366)
Mysql> Select DayOfYear (' 1998-02-03 ');
-> 34
MONTH (date)
Returns the number of months in date
Mysql> Select MONTH (' 1998-02-03 ');
-> 2
Dayname (date)
Return date is the day of the week (returned by English name)
Mysql> Select Dayname ("1998-02-05");
-> ' Thursday '
MonthName (date)
Return date is a few months (returned by English name)
Mysql> Select MonthName ("1998-02-05");
-> ' February '
Quarter (date)
Return date is the quarter ordinal of a year
Mysql> Select quarter (' 98-04-01 ');
-> 2
WEEK (Date,first)
Returns date is the week ordinal of a year (first default 0,first value 1 means Monday is the beginning of the week, 0 starts in Sunday)
Mysql> Select WEEK (' 1998-02-20 ');
-> 7
Mysql> Select WEEK (' 1998-02-20 ', 0);
-> 7
Mysql> Select WEEK (' 1998-02-20 ', 1);
-> 8
Year (date)
Returns the year of date (range from 1000 to 9999)
Mysql> Select year (' 98-02-03 ');
-> 1998
HOUR (Time)
The number of hours to return time (range 0 to 23)
Mysql> Select HOUR (' 10:05:03 ');
-> 10
MINUTE (Time)
Returns the number of minutes in time (range 0 to 59)
Mysql> Select MINUTE (' 98-02-03 10:05:03 ');
-> 5
SECOND (Time)
Returns the number of seconds in time (range is 0 to 59)
Mysql> Select SECOND (' 10:05:03 ');
-> 3
Period_add (P,n)
Increase n months to time P and return (P's format yymm or YYYYMM)
Mysql> Select Period_add (9801,2);
-> 199803
Period_diff (P1,P2)
Returns the number of months between periods P1 and P2 (P1 and P2 formats yymm or YYYYMM)
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)