Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.