MYSQL time function usage

Source: Internet
Author: User
Tags mysql manual
MYSQL time function usage
MYSQL time function usage test table: root @ test 16:50> desc t1; + ------- + ---------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ---------- + ------ + ----- + --------- + ------- + | id | int (11) | YES | NULL | t1 | datetime | YES | NULL | + ------- + ---------- + ------ + ----- + --------- + ------- + 2 rows in set (0.00 sec) root @ test 16:50> select * from t1; + ------ + ------------------- + | id | t1 | + ------ + ------------------- + | 1 | 12:27:12 | 2 | 12:27:12 | 3 | 12:27:12 | + ------ + ----------------- + (1) ), DATE_ADD (date, INTERVAL expr unit) root @ test 16:50> SELECT DATE_ADD (t1, INTERVAL-1 YEAR) FROM t1; + ----------------------------- + | DATE_ADD (t1, INTERVAL-1 YEAR) | + minute + | 12:27:12 | 12:27:12 | 12:27:12 | + --------------------------------------- + root @ test 17:21> SELECT DATE_ADD ('2017-01-02 ', INTERVAL 31 DAY ); + expiration + | DATE_ADD ('2017-01-02 ', INTERVAL 31 DAY) | + ------------------------------------------- + | 2008-02-02 | + expiration + (2), DATE_FORMAT (date, format)
There are many formats for format. you can use the MYSQL manual for details. This function allows time to display root @ test> SELECT DATE_FORMAT (t1, '% H: % I: % S') from t1; + ----------------------------- + | DATE_FORMAT (t1, '% H: % I: % S') | + --------------------------- + | 12:27:12 | 12:27:12 | 12:27:12 | + ----------------------------- + (3) datediff (expr1, expr2) used to calculate the number of days of the difference between the two time. root @ test> select datediff (t1, now () from t1; + ------------------ + | datediff (t1, now ()) | + -------------------- + |-23 |- 23 |-23 | + ------------------ + 4, returns the current date curdate (), curtime () returns the current time, now () returns the current date and time.
5. return year () in the date, month () in the date, day () in the date, and time () in the date (). Root @ test 17:08> select day (t1) from t1; + --------- + | day (t1) | + --------- + | 13 | 13 | 13 | + --------- + 3 rows in set (0.00 sec) root @ test 17:13> select time (t1) from t1; + ---------- + | time (t1) | + ---------- + | 12:27:12 | 12:27:12 | 12:27:12 | + ---------- + 3 rows in set (0.00 sec) root @ test 17:13> select month (t1) from t1; + ----------- + | month (t1) | + ----------- + | 5 | 5 | 5 | + ----------- + 3 rows in set (0.00 sec) root @ test> select year (t1) from t1; + ---------- + | year (t1) | + ---------- + | 2012 | 2012 | 2012 | + ---------- + 3 rows in set (0.00 sec) 6, week () calculate the number of weeks of the current year. root @ test> select week ('2017-06-05 '); + ------------------ + | week ('2017-06-05 ') | + -------------------- + | 23 | + -------------------- + 1 row in set (0.00 sec) author alang85bitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.