今天在做一个功能的时候,用到两个时间戳相减来做查询条件,由于其两个字段都是unsigned的,并两个的大小是不一样。所以直接相减查询的时候,
就出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的错误,因为结果可能会出现负数。。。。
过后查资料,发现可以通过下面的方法来解决:
- mysql> select lastvisit, lastactivity from pre_common_member_status limit 1;
- +------------+--------------+
- | lastvisit | lastactivity |
- +------------+--------------+
- | 1199200260 | 1198336989 |
- +------------+--------------+
- 1 row in set (0.01 sec)
- mysql> select lastvisit-lastactivity from pre_common_member_status limit 1;
- +------------------------+
- | lastvisit-lastactivity |
- +------------------------+
- | 863271 |
- +------------------------+
- 1 row in set (0.05 sec)
- mysql> select abs(lastvisit-lastactivity) from pre_common_member_status limit 1;
- +-----------------------------+
- | abs(lastvisit-lastactivity) |
- +-----------------------------+
- | 863271 |
- +-----------------------------+
- 1 row in set (0.03 sec)
- mysql> select lastactivity-lastvisit from pre_common_member_status limit 1;
- ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`discuz`.`pre_c
- ommon_member_status`.`lastactivity` - `discuz`.`pre_common_member_status`.`las
- tvisit`)'
- mysql> select cast(lastactivity as signed)-cast(lastvisit as signed) from pre_co
- mmon_member_status limit 1;
- +--------------------------------------------------------+
- | cast(lastactivity as signed)-cast(lastvisit as signed) |
- +--------------------------------------------------------+
- | -863271 |
- +--------------------------------------------------------+
- 1 row in set (0.02 sec)
- mysql> select abs(cast(lastactivity as signed)-cast(lastvisit as signed)) from p
- re_common_member_status limit 1;
- +-------------------------------------------------------------+
- | abs(cast(lastactivity as signed)-cast(lastvisit as signed)) |
- +-------------------------------------------------------------+
- | 863271 |
- +-------------------------------------------------------------+
- 1 row in set (0.00 sec)
- mysql>