The time function of MySQL

MySQL time function, put the view

1. The first day of the week, starting from Sunday mysql> select DayOfWeek (' 2015-05-25 '); +-------------------------+|                       DayOfWeek (' 2015-05-25 ') |+-------------------------+| 2 |+-------------------------+1 row in Set (0.00 sec) 2. The first day of the month mysql> Select DayOfMonth (' 2015-05-25 '); +------------- -------------+|                       DayOfMonth (' 2015-05-25 ') |+--------------------------+| |+--------------------------+1 Row in Set (0.00 sec) 3. The first day of the year mysql> Select DayOfYear (' 2015-05-25 '); +------------ -------------+|                     DayOfYear (' 2015-05-25 ') |+-------------------------+| 145 |+-------------------------+1 row in Set (0.00 sec) 4. Get weeks mysql> Select Dayname (' 2015-05-26 '); +----------------- ------+| Dayname (' 2015-05-26 ') |+-----------------------+| Tuesday |+-----------------------+1 row in Set (0.04 sec) 5. Get Month mysql> Select month (' 2015-05-26 '), Monthnam E (' 2015-05-26 '); +---------------------+-------------------------+| Month (' 2015-05-26 ') | MonthName (' 2015-05-26 ') |+---------------------+-------------------------+| 5 | May |+---------------------+-------------------------+1 row in Set (0.00 sec) 6. Get the week mysql> Select W Eek (' 2015-05-26 '); +--------------------+|                 Week (' 2015-05-26 ') |+--------------------+| |+--------------------+1 Row in Set (0.00 sec) 7. Get the quarter mysql> Select quarter (' 2015-06-26 '); +--------------------- --+|                     Quarter (' 2015-06-26 ') |+-----------------------+| 2 |+-----------------------+1 row in Set (0.00 sec) 8. View years mysql> Select year (' 2015-09-26 '); +--------------------+|               Year (' 2015-09-26 ') |+--------------------+| |+--------------------+1 Row in Set (0.00 sec) 9. Other mysql> Select year (' 2015-05-26 '), month (' 2015-05-26 '), Day (' 2015-05-26 ') +--------------------+---------------------+-------------------+| Year (' 2015-05-26 ') | Month (' 2015-05-26 ') |               Day (' 2015-05-26 ') |+--------------------+---------------------+-------------------+| 2015 |                5 | |+--------------------+---------------------+-------------------+1 row in Set (0.01 sec) 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) 10. View Current Time mysql> Select Current_time (), Sysdate (), Current_timestamp (), now (), +----------------+---------------------+------------------- --+---------------------+| Current_time () | Sysdate () | Current_timestamp () | Now () |+----------------+---------------------+---------------------+---------------------+| 00:29:05 | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 |+----------------+---------------------+---------------------+---------------------+1 row in Set (0.00 sec) Other similar built-in functions, now is a session-related, sysdate unrelated transaction current_timestamp (), Current_timestamp,localtime (), Localtime,localtimestamp,localtimestamp () 11.unix timestamp conversion mysql> Select Unix_ Timestamp (' 2015-05-26 15:38:22 '), Unix_timestamp (now ()); +---------------------------------------+--------------- --------+| Unix_timestamp (' 2015-05-26 15:38:22 ') |                            Unix_timestamp (now ()) |+---------------------------------------+-----------------------+|            1432679902 | 1432625945 |+---------------------------------------+-----------------------+1 row in Set (0.00 sec) mysql> Select From_unixtime (1432679902), From_unixtime (1432625945), +---------------------------+---------------------------+| From_unixtime (1432679902) | From_unixtime (1432625945) |+---------------------------+---------------------------+| 2015-05-26 15:38:22 | 2015-05-26 00:39:05 |+---------------------------+---------------------------+1 row in Set (0.00 sec) 12.Format%m month name (January ... December)%W week name (Sunday ... Saturday)%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )%Y year, number, 4-digit%Y year, number, 2-bit%a abbreviated weekday name (Sun ... Sat)%d days in the month, number (00 ... %e number of days in the month, number (0 ... %m months, numbers (01 ... %c month, number (1 ... %b Abbreviated month name (Jan ... DEC)%j Days of the year (001 ... 366)%H hours (00 ... %k hours (0 ...) %h hours (01 ...) %I) Hours (01 ... %l hours (1 ...) %i minutes, Numbers (00 ...) %r time, 12 hours (Hh:mm:ss [ap]m)%T time, 24 hours (hh:mm:ss)%s seconds (00 ... ()%s seconds (00 ... %p am or pm%w one days of the week (0=sunday ... 6=saturday)%u week (0 ... 52), here Sunday is the first day of the week%u week (0 ... 52), here Monday is the first day of the week, the percent character%mysql> select Now (), Date_format (now (), '%M%d%Y%b%a%d%e%j%i '), +---------------------+---- ---------------------------------------------+| Now () | Date_format (now (), '%M%d%Y%b%a%d%e%j%i ') |+---------------------+----------------------------------------------- --+| 2015-05-26 01:38:17 | May 26th Tue 146 |+---------------------+-------------------------------------------------+ 1 row in Set (0.00 sec) 13. Time Operation Mysql> Select Now (), Date_add(Now (), interval second), +---------------------+------------------------------------+| Now () | Date_add (now (), interval second) |+---------------------+------------------------------------+| 2015-05-27 00:22:20 | 2015-05-27 00:22:31 |+---------------------+------------------------------------+1 row in Set (0.00 sec) Mys Ql> Select Now (), Date_add (now (), Interval 1 hour), +---------------------+---------------------------------+| Now () | Date_add (now (), Interval 1 hour) |+---------------------+---------------------------------+| 2015-05-27 00:23:26 |  2015-05-27 01:23:26 |+---------------------+---------------------------------+1 row in Set (0.00 sec) mysql> Select Now (), Date_add (today (), Interval 1 day), +---------------------+--------------------------------+| Now () | Date_add (now (), Interval 1 day) |+---------------------+--------------------------------+| 2015-05-27 00:22:28 | 2015-05-28 00:22:28 |+---------------------+--------------------------------+1 row in Set (0.00 sec) mysql> Select Now (), Date_add (now (), Interval 1 month); +---------------------+----------------------------------+| Now () | Date_add (now (), Interval 1 month) |+---------------------+----------------------------------+| 2015-05-27 00:23:57 | 2015-06-27 00:23:57 |+---------------------+----------------------------------+1 row in Set (0.00 sec) MYSQL&G T Select Now (), Date_add (Today (), Interval-1 year), +---------------------+----------------------------------+| Now () | Date_add (now (), interval-1 year) |+---------------------+----------------------------------+| 2015-05-27 00:24:34 | 2014-05-27 00:24:34 |+---------------------+----------------------------------+1 row in Set (0.06 sec)

Time function for MySQL