"Mysql" Time processing

MySQL Time processing

Usually we have some time to change the demand, such as to count the revenue of a certain period of time, such as to intercept a certain time of year, such as to calculate the day of the week according to a certain date, these can be implemented by the time function of MySQL is easy to achieve. Because I am not familiar with MySQL functions, and usually call these time functions, this side of a little summary to query.

– Returns the current time

MySQL>Select Now (), Date (now ()), sysdate ();

+---------------------+-------------+---------------------+

| Now () | Date (now ()) | Sysdate () |
+---------------------+-------------+---------------------+
| 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |
+---------------------+-------------+---------------------+
1 row in Set (0.00 sec)

MySQL>Select curdate (), curdate ()+0, Curtime (), Curtime ()+0 

+---------------------+-------------+---------------------+

| Curdate () | Curdate (+0) | Curtime () | Curtime (+0) |
+---------------------+-------------+---------------------+
| 2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |
+---------------------+-------------+---------------------+

– Return date the last day of the month

MySQL>Select last_day ('2008-12-02');

+------------------------+

| Last_day (' 2008-12-02 ') |
+------------------------+
| 2008-12-31 |
+------------------------+
1 row in Set (0.00 sec)

– The day of the week to return the date

MySQL>Select dayname ('2008-12-02'), DayOfWeek ('  2008-12-02');

+-----------------------+-------------------------+
| Dayname (' 2008-12-02 ') | DayOfWeek (' 2008-12-02 ') |
+-----------------------+-------------------------+
| Tuesday | 3 |
+-----------------------+-------------------------+
1 row in Set (0.00 sec)

– Return date of year, month, day

MySQL>Selectmonth('2008-12-02'),year ('  2008-12-02'),Day ('2008-12-02');

+---------------------+--------------------+-------------------+

| Month (' 2008-12-02 ') | Year (' 2008-12-02 ') | Day (' 2008-12-02 ') |
+---------------------+--------------------+-------------------+
| 12 | 2008 | 2 |
+---------------------+--------------------+-------------------+
1 row in Set (0.00 sec)

– Returns the hour, minutes, seconds of the date

MySQL>Select Hour ('10:05:03'), Minute ('10:05:03  '), second ('10:05:03'

+------------------+--------------------+--------------------+

| Hour (' 10:05:03 ') | Minute (' 10:05:03 ') | Second (' 10:05:03 ') |
+------------------+--------------------+--------------------+
| 10 | 5 | 3 |
+------------------+--------------------+--------------------+
1 row in Set (0.00 sec)

1.subdate (d,t): Start time plus a period of time (Year,month,day ... ）

MySQL>Select date_add ('1998-01-02'), Adddate ('1998-01-02' )

+-----------------------------------------+---------------------------+
| Date_add (' 1998-01-02 ', Interval day) | Adddate (' 1998-01-02 ', 31) |
+-----------------------------------------+---------------------------+
| 1998-02-02 | 1998-02-02 |
+-----------------------------------------+---------------------------+
1 row in Set (0.00 sec)

MySQL>Select date_add ('1998-01-02'2year);

+-----------------------------------------------------+
| Date_add (' 1998-01-02 ', Interval 2 year)
+-----------------------------------------------------+
| 2000-01-02
+-----------------------------------------------------+
1 row in Set (0.00 sec)

MySQL>Select date_add ('1998-01-02'2 hour);

+-----------------------------------------------------+
| Date_add (' 1998-01-02 ', Interval 2 hour)
+-----------------------------------------------------+
| 1998-01-02 02:00:00
+-----------------------------------------------------+
1 row in Set (0.00 sec)

2.subdate (d,t): Start time minus a period of time

MySQL>Select subdate ('1998-01-02'), Subdate ('1998-01-02');

+----------------------------------------+---------------------------+
| Subdate (' 1998-01-02 ', Interval day) | Subdate (' 1998-01-02 ', 31) |
+----------------------------------------+---------------------------+
| 1997-12-02 | 1997-12-02 |
+----------------------------------------+---------------------------+
1 row in Set (0.00 sec)

3.addtime (d,t): Start time D join time t

 mysql>  select  addtime ( '    23:59:50   ' ,  

+-------------------------------------------+--------------------------------+
| Addtime (' 1997-12-31 23:59:50 ', ' 00:00:05 ') | Addtime (' 23:59:50 ', ' 00:00:05 ') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:55 | 23:59:55 |
+-------------------------------------------+--------------------------------+
1 row in Set (0.00 sec)

4.subtime (d,t): Start time D minus time t

 mysql>  select  subtime ( '    23:59:50   ' ,  

+-------------------------------------------+--------------------------------+
| Subtime (' 1997-12-31 23:59:50 ', ' 00:00:05 ') | Subtime (' 23:59:50 ', ' 00:00:05 ') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45 | 23:59:45 |
+-------------------------------------------+--------------------------------+
1 row in Set (0.00 sec)

5.datediff (D1,D2): Returns the number of days between the start time D1 and the end time D2

MySQL>Selectdatediff('1997-12-31 23:59:59',' 1997-12-30 ');

+----------------------------------------------+
| DateDiff (' 1997-12-31 23:59:59 ', ' 1997-12-30 ') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in Set (0.00 sec)

6.date_format (Date,format): Displays the format of the date value according to the format string

MySQL>Select date_format ('2008-12-02 22:23:00'%y%m %m%h:%i:%s');

+---------------------------------------------------------+
| Date_format (' 2008-12-02 22:23:00 ', '%y%m%m%h:%i:%s ') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00 |
+---------------------------------------------------------+
1 row in Set (0.00 sec)

7.str_to_date (Str,format) string conversion to TIME

MySQL>Select str_to_date ('04/31/2004'%m/%d/%y%h:% I:s');

+-----------------------------------------------+
| Str_to_date (' 04/31/2004 ', '%m/%d/%y%h:%i:s ') |
+-----------------------------------------------+
| 2004-04-31 00:00:00 |
+-----------------------------------------------+
1 row in Set (0.00 sec)

8.timestamp (expr), timestamp (EXPR,EXPR2):
For a single parameter, the function returns the date or datetime expression expr as a DateTime value. For two parameters, it adds the time expression expr2 to the date or datetime expression expr, returning Theresult as a datetime value

 mysql>  select  timestamp  ( 2003-12-31   ' ), timestamp  ( " 2003-12-31 12:00:00  , "  12:00:00  ); 

+-------------------------+---------------------------------------------+
| Timestamp (' 2003-12-31 ') | Timestamp (' 2003-12-31 12:00:00 ', ' 12:00:00 ') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |
+-------------------------+---------------------------------------------+
1 row in Set (0.00 sec)

9. Take the day 0:0, next day 0:0

MySQL>Selecttimestamp(date (Sysdate ())),timestamp(adddate (Date (Sysdate () ),1

+----------------------------+---------------------------------------+
| Timestamp (date (sysdate ())) | Timestamp (adddate (Date (Sysdate ()), 1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in Set (0.00 sec)

"Mysql" Time processing