How to realize the function of time subtraction in mysql
This article introduces how to achieve the time subtraction function in mysql, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.
First, take a look at the error phenomenon as follows: article 1 is correct, while articles 2 and 3's t2-t1 are incorrect:
[sql]
Mysql > select T1 from mytest t2recoveryt2washi T1 from mytest
+-+
| | T1 | T2 | t2-t1 | |
+-+
| | 2013-04-21 16:59:33 | 2013-04-21 16:59:43 | 10 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:00:33 | 4100 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 |
+-+
3 rows in set
All the test scripts are as follows:
[sql]
-- create tables
Mysql > CREATE TABLE mytest (
T1 datetime
T2 datetime
)
Query OK, 0 rows affected
-- insert test record
Mysql > insert into mytest (T1 and T2) values ('2013-04-21 16 purl 5933')
Query OK, 1 row affected
Mysql > insert into mytest (T1 and T2) values ('2013-04-21 16-15 59-33-12 13-04-21 17-00-33)
Query OK, 1 row affected
Mysql > insert into mytest (T1 and T2) values ('2013-04-21 16-15 59-33-12 13-04-21 17-15 59-15 35')
Query OK, 1 row affected
-- verify the result
Mysql > select T1 from mytest t2recoveryt2washi T1 from mytest
+-+
| | T1 | T2 | t2-t1 | |
+-+
| | 2013-04-21 16:59:33 | 2013-04-21 16:59:43 | 10 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:00:33 | 4100 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 |
+-+
3 rows in set
In fact, the time subtraction of mysql is to do an implicit conversion operation to convert time to integers, but not to use unix_timestamp conversion, but to directly put together the year, month, day, minute and second, such as 2013-04-2116: 59:33 directly converted to 20130421165933, because the time is not decimal, so the final result is meaningless, which is also led to the above results.
[sql]
Mysql > select T1
T2
Convert (T1, UNSIGNED INTEGER) ct1
Convert (T2, UNSIGNED INTEGER) ct2
T2-t1
Convert (T2, UNSIGNED INTEGER)-convert (T1, UNSIGNED INTEGER) diff0
From mytest
+-+ +
| | T1 | T2 | ct1 | ct2 | t2-t1 | diff0 | |
+-+ +
| | 2013-04-2116: 59:33 | 2013-04-2116: 59:43 | 20130421165933 | 20130421165943 | 10 | 10 |
| | 2013-04-2116: 59:33 | 2013-04-2117: 00:33 | 20130421165933 | 20130421170033 | 4100 | 4100 |
| | 2013-04-2116: 59:33 | 2013-04-2117: 59:35 | 20130421165933 | 20130421175935 | 10002 | 10002 |
+-+ +
3 rows in set
To get the correct time minus seconds, there are three ways:
1. Time_to_sec (timediff (T2, T1))
2. Timestampdiff (second, T1, T2)
3. Unix_timestamp (T2)-unix_timestamp (T1)
[sql]
-- Test script
Mysql > select T1
T2
T2-t1
Time_to_sec (timediff (T2, T1)) diff1
Timestampdiff (second, T1, T2) diff2
Unix_timestamp (T2)-unix_timestamp (T1) diff3
From mytest
+-+ +
| | T1 | T2 | t2-t1 | diff1 | diff2 | diff3 | |
+-+ +
| | 2013-04-21 16:59:33 | 2013-04-21 16:59:43 | 10 | 10 | 10 | 10 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:00:33 | 4100 | 60 | 60 | 60 |
| | 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 | 3602 | 3602 | 3602 |
+-+ +
3 rows in set
This problem was reported in the version of mysql4.0 in 2003, but mysql officials did not think it was bug, because they thought that mysql did not support direct time subtraction and should be dealt with by special functions, so it has not been corrected. But I think this can easily lead to usage errors, either reporting errors directly or displaying correct results.
On how to achieve time subtraction in mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
