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) www.2cto.com 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) | www.2cto.com + expiration + | 2008-02-02 | + ----------------------------------------- + (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; + --------- + www.2cto.com | 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) | + ---------- + www.2cto.com | 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 alang85