今天遇到一个问题,需要根据mysql表里的两个时间字段做查询,要求查询两个时间字段之间的时间差小于等于2秒的数据,datetime型的字段能不能相减呢?我们试试:
mysql> select ended_at, begun_at, ended_at - begun_at from blogs order by id desc limit 10;
+---------------------+---------------------+---------------------+
| ended_at | begun_at | ended_at - begun_at |
+---------------------+---------------------+---------------------+
| 2015-10-01 11:27:30 | 2015-08-25 11:27:30 | 176000000.000000 |
| 2015-10-01 11:12:53 | 2015-08-25 11:12:53 | 176000000.000000 |
| 2015-10-01 11:11:06 | 2015-08-25 11:11:06 | 176000000.000000 |
| 2015-10-01 11:09:14 | 2015-08-25 11:09:14 | 176000000.000000 |
| 2015-10-01 11:03:53 | 2015-08-25 11:03:53 | 176000000.000000 |
| 2015-08-29 10:30:00 | 2015-08-06 10:30:00 | 23000000.000000 |
| 2015-08-29 10:30:00 | 2015-08-06 10:30:00 | 23000000.000000 |
| 2015-08-09 09:00:00 | 2015-08-06 09:00:00 | 3000000.000000 |
| 2015-08-09 09:00:00 | 2015-08-06 09:00:00 | 3000000.000000 |
| 2015-08-29 14:50:00 | 2015-08-22 14:50:00 | 7000000.000000 |
+---------------------+---------------------+---------------------+
10 rows in set (0.00 sec)
看出来还是有一定的规律的,但是这个规律对我们的问题没有带来解决方案。
有过一定的编程经验的都知道,时间点可以转换一个整数,它是距离1970-01-01 00:00:00这个时间点的秒数,那么有什么办法可以把这个datetime转换为秒,然后再进行对比呢?
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1460381256 |
+------------------+
1 row in set (0.00 sec)
到此,我们可以用这个函数处理时间了:
mysql> select ended_at, UNIX_TIMESTAMP(ended_at) from blogs order by id desc limit 10;
+---------------------+--------------------------+
| ended_at | UNIX_TIMESTAMP(ended_at) |
+---------------------+--------------------------+
| 2015-10-01 11:27:30 | 1443670050 |
| 2015-10-01 11:12:53 | 1443669173 |
| 2015-10-01 11:11:06 | 1443669066 |
| 2015-10-01 11:09:14 | 1443668954 |
| 2015-10-01 11:03:53 | 1443668633 |
| 2015-08-29 10:30:00 | 1440815400 |
| 2015-08-29 10:30:00 | 1440815400 |
| 2015-08-09 09:00:00 | 1439082000 |
| 2015-08-09 09:00:00 | 1439082000 |
| 2015-08-29 14:50:00 | 1440831000 |
+---------------------+--------------------------+
10 rows in set (0.00 sec)我们用ruby去验证一下:
mysql> select ended_at, UNIX_TIMESTAMP(ended_at) from blogs order by id desc limit 10;
+---------------------+--------------------------+
| ended_at | UNIX_TIMESTAMP(ended_at) |
+---------------------+--------------------------+
| 2015-10-01 11:27:30 | 1443670050 |
| 2015-10-01 11:12:53 | 1443669173 |
| 2015-10-01 11:11:06 | 1443669066 |
| 2015-10-01 11:09:14 | 1443668954 |
| 2015-10-01 11:03:53 | 1443668633 |
| 2015-08-29 10:30:00 | 1440815400 |
| 2015-08-29 10:30:00 | 1440815400 |
| 2015-08-09 09:00:00 | 1439082000 |
| 2015-08-09 09:00:00 | 1439082000 |
| 2015-08-29 14:50:00 | 1440831000 |
+---------------------+--------------------------+
10 rows in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
[chenyafei@develop ~]$ irb
1.9.3-p551 :001 > Time.a
Time.allocate Time.ancestors Time.at Time.autoload Time.autoload?
1.9.3-p551 :001 > Time.at(1443670050)
=> 2015-10-01 11:27:30 +0800
1.9.3-p551 :002 > Time.at(1443669173)
=> 2015-10-01 11:12:53 +0800
1.9.3-p551 :003 > Time.at(1443669066)
=> 2015-10-01 11:11:06 +0800
1.9.3-p551 :004 > Time.at(1443668954)
=> 2015-10-01 11:09:14 +0800
既然能得到时间的秒数,查询语句就简单多了:
mysql> select begun_at, ended_at from blogs where (UNIX_TIMESTAMP(ended_at) - UNIX_TIMESTAMP(begun_at)) < 2 limit 10;
+---------------------+---------------------+
| begun_at | ended_at |
+---------------------+---------------------+
| 2015-06-01 10:30:00 | 2015-06-01 10:30:01 |
| 2015-06-01 10:30:00 | 2015-06-01 10:30:00 |
| 2015-06-01 10:30:00 | 2015-06-01 10:30:01 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:01 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:01 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:01 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:00 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:01 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:00 |
| 2015-06-02 10:30:00 | 2015-06-02 10:30:01 |
+---------------------+---------------------+
10 rows in set (0.30 sec)
哈哈,查出来了吧。
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html这里有更多的日期处理函数,就不一一介绍了,有需要的可以自己查看。