Square usage of MYSQL time function
This article introduces the relevant knowledge of "MYSQL time function usage". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
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 | 2012-05-13 12:27:12 |
| | 2 | 2012-05-13 12:27:12 |
| | 3 | 2012-05-13 12:27:12 |
+-+ +
(1), DATE_ADD (date,INTERVAL expr unit)
Root@test 16:50 > SELECT DATE_ADD (T1 mensural-1 YEAR) FROM T1
+-+
| | DATE_ADD (T1 immediate-1 YEAR) | |
+-+
| | 2011-05-13 12:27:12 |
| | 2011-05-13 12:27:12 |
| | 2011-05-13 12:27:12 |
+-+
Root@test 17:21 > SELECT DATE_ADD ('2008-01-02, INTERVAL 31 DAY)
+-+
| | DATE_ADD ('2008-01-02, INTERVAL 31 DAY) | |
+-+
| | 2008-02-02 |
+-+
(2), DATE_FORMAT (date,format)
There are many format formats, specific can be MYSQL manual. This function allows time to be displayed in a certain format
Root@test 17:00 > SELECT DATE_FORMAT (T1,'% HGV% ifrom% s') from T1
+-+
| | DATE_FORMAT (T1,'% HRV% iRU% s') |
+-+
| | 12:27:12 |
| | 12:27:12 |
| | 12:27:12 |
+-+
(3) datediff (expr1,expr2) is used to calculate the number of days of difference between two times.
Root@test 17:01 > select datediff (T1 focus now ()) from T1
+-+
| | datediff (T1 focus now ()) | |
+-+
| |-23 |
| |-23 |
| |-23 |
+-+
4. Returns the current date curdate (), curtime () returns the current time, and now () returns the current date and time.
5. Return the year year () in the date, month () in the middle month of 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 17:13 > select year (T1) from T1
+-+
| | year (T1) | |
+-+
| | 2012 |
| | 2012 |
| | 2012 |
+-+
3 rows in set (0.00 sec)
6Jing week () calculates how many weeks the current date is this year
Root@test 17:16 > select week ('2012-06-05')
+-+
| | week ('2012-06-05') | |
+-+
| | 23 |
+-+
1 row in set (0.00 sec)
This is the end of the content of "MYSQL time function usage". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
