MySQL时间相减问题

来源：互联网发布：知乎跳槽才能涨工资编辑：程序博客网时间：2024/05/24 03:37

[sql]

mysql> select t1,t2,t2-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

全部测试脚本如下：

[sql]

--创建表

mysql> CREATE TABLE mytest (

t1 datetime,

t2 datetime

);

Query OK, 0 rows affected

--插入测试记录

mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 16:59:43');

Query OK, 1 row affected

mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 17:00:33');

Query OK, 1 row affected

mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 17:59:35');

Query OK, 1 row affected

--验证结果

mysql> select t1,t2,t2-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

实际是mysql的时间相减是做了一个隐式转换操作，将时间转换为整数，但并不是用unix_timestamp转换，而是直接把年月日时分秒拼起来，如2013-04-21 16:59:33 直接转换为20130421165933，由于时间不是十进制，所以最后得到的结果没有意义，这也是导致上面出现坑爹的结果。

[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-21 16:59:33|2013-04-21 16:59:43|20130421165933|20130421165943| 10| 10|

|2013-04-21 16:59:33|2013-04-21 17:00:33|20130421165933|20130421170033| 4100| 4100|

|2013-04-21 16:59:33|2013-04-21 17:59:35|20130421165933|20130421175935|10002|10002|

+-------------------+-------------------+--------------+--------------+-----+-----+

3 rows in set

要得到正确的时间相减秒值，有以下3种方法：

1、time_to_sec(timediff(t2, t1)),

2、timestampdiff(second, t1, t2),

3、unix_timestamp(t2) -unix_timestamp(t1)

[sql]

--测试脚本

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

这个问题2003年就有人在mysql4.0的版本时反馈，但mysql官方并不认为是bug，因为他们认为mysql并不支持时间直接相减操作，应该用专用函数处理，所以一直没有修正。但我认为这个很容易导致使用错误，要么就直接报错，要么显示正确的结果。

mysql的TIMESTAMPDIFF函数可以返回两个日期之间的时间差。如下语法：

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

参数：

unit 返回时间差的单位
datetime_expr1 日期
datetime_expr2 日期

时间单位如下：

MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'

2.MySQL的unix_timestamp函数：

将时间转化为时间戳。（date 类型数据转换成 timestamp 形式整数）

没传时间参数则取当前时间的时间戳

+------------------+
| unix_timestamp() |
+------------------+
| 1361586358 |
+------------------+
1 row in set (0.01 sec)

mysql> select unix_timestamp('2013-01-01 10:10:10');
+---------------------------------------+
| unix_timestamp('2013-01-01 10:10:10') |
+---------------------------------------+
| 1357006210 |
+---------------------------------------+
1 row in set (0.00 sec)

2.from_unixtime

将timestamp 形式整数转化为 date类型

mysql> select from_unixtime(1355272360);
+---------------------------+
| from_unixtime(1355272360) |
+---------------------------+
| 2012-12-12 08:32:40 |
+---------------------------+
1 row in set (0.00 sec)

当然也可以指定输出的时间格式：

mysql> select from_unixtime(1355272360,'%Y%m%d');
+------------------------------------+
| from_unixtime(1355272360,'%Y%m%d') |
+------------------------------------+
| 20121212 |
+------------------------------------+

3.关于mysql 时间戳的限制

目前timestamp 所能表示的范围在 1970 - 2038之间。

超过这个范围得到的时间将会溢出得到的时间是null.

mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0) |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+

mysql> select from_unixtime(2147483647);
+---------------------------+
| from_unixtime(2147483647) |
+---------------------------+
| 2038-01-19 11:14:07 |
+---------------------------+
1 row in set (0.00 sec)

3.MySQL time_to_sec函数：

time_to_sec()函数仅仅计算了时间部分的秒数，没有把年月日的秒数计算进去。因为这个特点，导致了我程序的一个bug。

这段代码本来想统计客服的响应时间。但是碰到这种极品数据的时候，例如， responseTime：2013-1-8 00:00:01 ，firstMsgTime: 2013-1-7 23:59:50，程序就返回负数了。

mysql时间函数——UNIX_TIMESTAMP（）,这个函数返回的是自1970年以来的秒数。用这个函数才能正确的计算两个时间点的秒数差。

time_to_sec(time) 返回 time 参数，转换成秒。

mysql> SELECT TIME_TO_SEC('22:23:00');+---------------------------------------------------------+| TIME_TO_SEC('22:23:00')                                 |+---------------------------------------------------------+| 80580                                                   |+---------------------------------------------------------+1 row in set (0.00 sec)

阅读全文

0 0