MySQL time and date function learning

MySQL time and date function learning

In order to facilitate the use of the more Intelligent MySQL Client tool MYCLI, the introduction of this tool can be in another article (http://watchman110.blog.51cto.com/9194028/1687953) to understand!

1,now () function to get the current date and time

MySQL [email protected]:(None) > SELECT now () +---------------------+| Now () | | ---------------------|| 2015-08-25 10:47:54 |+---------------------+

2,sysdate () function to get the current date and time

MySQL [email protected]:(none) > SELECT sysdate () +---------------------+| Sysdate () | | ---------------------|| 2015-08-25 10:49:43 |+---------------------+

Mysql [email protected]:(None) > select now (), Sleep (2), now () +---------------------+----- -------+---------------------+| now ()           |    sleep (2)  | now ()   | | ---------------------+------------+---------------------||  2015-08-25 10:51:18 |       0 | 2015-08-25  10:51:18 |+---------------------+------------+---------------------+mysql [email protected]:( None) > select sysdate (), Sleep (2), sysdate () +---------------------+------------+------------------- --+| sysdate ()        |   sleep (2)  | SYSDATE ()  | | ---------------------+------------+---------------------||  2015-08-25 10:52:01 |       0 | 2015-08-25  10:52:03 |+---------------------+------------+---------------------+ 

3,current_time (), Curtime () function, function to get the current time

MySQL [email protected]:(none) > SELECT current_time () +------------------+| Current_time () | | ------------------|| 10:57:47 |+------------------+mysql [email protected]:(none) > SELECT curtime () +-------------+| Curtime () | | -------------|| 10:57:55 |+-------------+

4,current_date (), curdate () function, function to get the current date

MySQL [email protected]:(none) > SELECT current_date () +------------------+| Current_date () | | ------------------|| 2015-08-25 |+------------------+mysql [email protected]:(none) > SELECT curdate () +-------------+| Curdate () | | -------------|| 2015-08-25 |+-------------+

5,current_timestamp () function to get the current timestamp function

MySQL [email protected]:(none) > SELECT current_timestamp () +-----------------------+| Current_timestamp () | | -----------------------|| 2015-08-25 11:00:21 |+-----------------------+

6,date_format (Date,format) function, date format conversion function, convert date to string form

MySQL [email protected]:(none) > SELECT curdate (), Date_format (Curdate (), '%y%m%d '), +-------------+--------------- --------------------+|   Curdate () | Date_format (Curdate (), '%y%m%d ') | | -------------+-----------------------------------||                          2015-08-25 | 20150825 |+-------------+-----------------------------------+

7,time_format (Time,format) function, time conversion function, convert time to string form

MySQL [email protected]:(none) > SELECT curtime (), Time_format (Curtime (), '%h%i%s ') +-------------+---------------- -------------------+|   Curtime () | Time_format (Curtime (), '%h%i%s ') | | -------------+----------------------------------- ||                 11:15:17 | 111517 |+-------------+-----------------------------------+

The inverse function of the above function str_to_date () function

Mysql [email protected]:(None) > select str_to_date (' 20150311 ', '%y%m%d ') +--------------- ---------------------+| str_to_date (' 20150311 ', '%y%m%d ')   | |----------------------------- -------||  2015-03-11                  |+------------------------------------+mysql [email protected]:(None) > select  str_to_date (' 121409 ', '%h%i%s ') +----------------------------------+| str_to_date (' 121409 ', '%h%i%s ')    | | ----------------------------------||  12:14:09                          |+----------------------------------+mysql [email  protected]:(None) > select str_to_date (' 20150903120000 ', '%y%m%d%h%i%s ') +-------------------- ----------------------------+| str_to_date (' 20150903120000 ','%y%m%d%h%i%s ')    | | ------------------------------------------------||  2015-09-03 12:00:00                             |+------------------ ------------------------------+

8,unix_timestamp () function, a function that converts time into a timestamp

Mysql [email protected]:(None) > select unix_timestamp () +--------------------+|   unix_timestamp ()  | | --------------------||       1440474909 |+--------------------+mysql [email protected]:( None) > select unix_timestamp (' 2015-09-03 ') +--------------------------------+|    Unix_timestamp (' 2015-09-03 ')  | | --------------------------------||                       1441209600 |+--------------------------------+mysql [email protected]:(None) > select unix_timestamp (' 2015-09-03 01:00:00 ') +-----------------------------------------+|    unix_timestamp (' 2015-09-03 01:00:00 ')  | | -----------------------------------------||                                1441213200 |+----------------------------- ------------+9,from_unixtime (unix_timestamp) function, convert timestamp to time string mysql [email protected]:(None) >  Select from_unixtime (1441209600) +-----------------------------+| from_unixtime (1441209600)     | | -----------------------------||  2015-09-03 00:00:00         |+------------------------ -----+mysql [email protected]:(None) > select from_unixtime (1441209600, '%Y%m%d %H:% i:%s ') +-----------------------------------------------+| from_unixtime (1441209600, '%y%m%d %h:%i:%s ')    | | -----------------------------------------------||  20150903 00:00:00                              |+------------ -----------------------------------+10,date_add () function, date calculation function mysql [email protected]:(none) > select date_ Add (now (), interval 1 year) +-----------------------------------+| date_add (now (), interval 1  year)    | | -----------------------------------||  2016-08-25 13:53:16                |+-----------------------------------+mysql [email protected]:(None) > select  date_add (now (), interval 1 month) +------------------------------------+| date_add (now (), Interval 1 month)    | | ------------------------------------||  2015-09-25 13:54:38                 |+------------------------------------+mysql [email protected]:(None) >  Select date_add (now (), Interval 1 week) mysql [email protected]:(NoNE) > select date_add (now (), Interval 1 day) mysql [email protected]:(None) >  select date_add (now (), Interval 1 hour) mysql [email protected]:(None) >  Select date_add (now (), Interval 1 minute) mysql [email protected]:(None) >  Select date_add (now (), interval 1 second) empathy  -1  refers to a year/month/day/day/Time/minute/second ago Mysql [email  protected]:(None) > select date_add (now (), interval -1 year) +---------------------- --------------+| date_add (now (), interval -1 year)    | | ------------------------------------||  2014-08-25 13:57:46                 |+------------------------------------+11,datediff (date1,date2), date subtraction function, returns the number of days mysql [ Email protected]:(None) > select datediff (' 2015-09-03 ', ' 2015--8-25 ') +------------------------ ---------------+|   datediff (' 2015-09-03 ', ' 2015--8-25 ')  | | ---------------------------------------||                                        9 |+---------------------------------------+mysql [email protected]:(None) >  Select datediff (' 2015--8-25 ', ' 2015-09-03 ') +---------------------------------------+|    DateDiff (' 2015--8-25 ', ' 2015-09-03 ')  | | ---------------------------------------||                                      -9  |+---------------------------------------+12,timediff (time1,time2), the time subtraction function, returns the difference between the values Mysql [email  protected]:(None) > select timediff (' 12:37:33 ', ' 12:35:32 ') +-----------------------------------+| timediff (' 12:37:33 ', ' 12:35:32 ')     || -----------------------------------||  0:02:01                            |+-----------------------------------+mysql  [email protected]:(None) > select timediff (' 12:35:32 ', ' 12:37:33 ') +--------------------- --------------+| timediff (' 12:35:32 ', ' 12:37:33 ')    | | -----------------------------------||  -1 day, 23:57:59                   |+-----------------------------------+13,timestamp () timestamp conversion, increment, decrement function mysql [email  protected]:(None) > select timestamp (' 2013-08-25 '); +---------------------------+|  Timestamp (' 2013-08-25 ')    | | ---------------------------||  2013-08-25 00:00:00       |+---------------------------+mysql [email  Protected]:(None) > select timestamp (' 2013-08-25 ', ' 01:00:00 '); +----------------------------------- ---+| timestamp (' 2013-08-25 ', ' 01:00:00 ')    | | --------------------------------------||  2013-08-25 01:00:00                   |+--------------------------------------+14,timestampadd (), function similar to  date_add () , strong in Date_add () mysql [email protected]:(None) > select timestampdiff (year, ' 2013-01-01 ', ' 2015-01-01 ') +-------------------------------------------------+|   timestampdiff (year, ' 2013-01-01 ', ' 2015-01-01 ')  | | -------------------------------------------------||                                                  2 |+-------------------------------------------------+mysql [email protected]:(None ) > select timestampdiff (month, ' 2013-01-01 ', ' 2015-03-01 ') +---------------------------------------- ----------+|   timestampdiff (month, ' 2013-01-01 ', ' 2015-03-01 ')  | | --------------------------------------------------||                                                  26 |+---------------------------------- ----------------+mysql [email protected]:(None) > select timestampdiff (Day, ' 2013-01-01 ', ' 2015-03-01 ') +------------------------------------------------+|   timestampdiff (day, '2013-01-01 ', ' 2015-03-01 ')  | | ------------------------------------------------||                                               789 |+------------------------------------------------+ Mysql [email protected]:(None) > select timestampdiff (hour, ' 2013-01-01 ', ' 2015-03-01 ') +-- -----------------------------------------------+|   timestampdiff (hour, ' 2013-01-01 ', ' 2015-03-01 ')  | | -------------------------------------------------||                                              18936 |+-------------------------------------------------+mysql [email protected]:(None) > select timestampdiff (second, ' 2013-01-01 ', ' 2015-03-01 ') +---------------------------------------------------+|   timestampdiff (second, ' 2013-01-01 ', ' 2015-03-01 ')  | | ---------------------------------------------------||                                             68169600 |+---------------------------------------------------+

This article is from the "time to cook the Rain" blog, please be sure to keep this source http://watchman110.blog.51cto.com/9194028/1688046

MySQL time and date function learning