如何将 MySQL 中的毫秒数与给定日期进行比较
问题描述:
使用
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0
我有一个这样定义的表:
I have a table defined like this:
+-----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | | |
| user_id | bigint(20) | NO | MUL | | |
| directory_id | bigint(20) | NO | MUL | | |
| attribute_name | varchar(255) | NO | | | |
| attribute_value | varchar(255) | YES | | NULL | |
| attribute_lower_value | varchar(255) | YES | | NULL | |
+-----------------------+--------------+------+-----+---------+-------+
为我最后一次验证时间选择属性值返回
Selecting the attribute_value for my last authenticated time returns
+-------------------+-----------------+
| attribute_name | attribute_value |
+-------------------+-----------------+
| lastAuthenticated | 1330380013284 |
+-------------------+-----------------+
如果我使用 http://www.epochconverter.com/ 验证该值
If I verify the value using http://www.epochconverter.com/ it says
Assuming that this timestamp is in milliseconds:
GMT: Mon, 27 Feb 2012 22:00:13 GMT
但是如果我尝试以下代码行
But if I try the following line of code
mysql> select from_unixtime('1330380013284');
+--------------------------------+
| from_unixtime('1330380013284') |
+--------------------------------+
| NULL |
+--------------------------------+
任何人都知道我在这里遗漏了什么?
Anyone who immediately knows what I'm missing here?
答
你需要除以 1000 并去掉引号,否则你的日期相差很远
You need to divide by 1000 as well as remove the quotes, otherwise your dates are way off
SELECT FROM_UNIXTIME(theField/1000);
SELECT FROM_UNIXTIME(1330380013284/1000);
请参阅文档 - 请注意 MySQL 方法返回秒,而不是像您使用的毫秒:
See the documentation - notice the MySQL methods return SECONDS, not milliseconds like you are using:
If called with no argument, returns a Unix timestamp
(SECONDS since '1970-01-01 00:00:00' UTC) as an unsigned integer