//更新时期超过30分钟的订单
update si_order_main
set MainOrder_IsSuccess=3
where MainOrder_IsSuccess=0
and
(timediff(MainOrder_Time,now())+0)>1800
//筛选时期超过30分钟的订单
select *
from si_order_main
where
((timediff(MainOrder_Time,now())+0)>1800)
//选择没问题
//更新时
//发生Truncated incorrect time value错误,
MainOrder_Time都是用now()插入的,为什么有错误
CREATE TABLE `si_order_main` (
`MainOrder_Time` datetime default NULL COMMENT '下订单时间',
`MainOrder_IsSuccess` int(11) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--就这个数据,当年的没问题
2009-12-10 16:13:04 0
-> `MainOrder_Time` datetime default NULL COMMENT '下订单时间',
-> `MainOrder_IsSuccess` int(11) default '0'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)mysql> update si_order_main
-> set MainOrder_IsSuccess=3
-> where MainOrder_IsSuccess=0
-> and
-> (timediff(MainOrder_Time,now())+0)>1800;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 0 Changed: 0 Warnings: 0mysql>
insert into si_order_main2
values
(
'2010-01-04 16:13:04',0
)
insert into si_order_main
values
(
'2010-01-04 16:13:04',0
)
-> from si_order_main;
+--------------------------------+------------------------------------+
| timediff(MainOrder_Time,now()) | (timediff(MainOrder_Time,now())+0) |
+--------------------------------+------------------------------------+
| -838:59:59 | -8385959.000000 |
+--------------------------------+------------------------------------+
1 row in set, 2 warnings (0.06 sec)mysql>
+---------------------+---------------------+
| MainOrder_Time | MainOrder_IsSuccess |
+---------------------+---------------------+
| 2010-01-04 16:13:04 | 0 |
+---------------------+---------------------+
1 row in set (0.00 sec)mysql> update si_order_main
-> set MainOrder_IsSuccess=3
-> where MainOrder_IsSuccess=0
-> and UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(MainOrder_Time)>1800;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from si_order_main;
+---------------------+---------------------+
| MainOrder_Time | MainOrder_IsSuccess |
+---------------------+---------------------+
| 2010-01-04 16:13:04 | 3 |
+---------------------+---------------------+
1 row in set (0.00 sec)mysql>