如何将 MySQL 中的毫秒数与给定日期进行比较 [英] How to compare milliseconds in MySQL with a given date

问题描述

使用

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:

UNIX_TIMESTAMP 的 MySQL 文档

If called with no argument, returns a Unix timestamp 
(SECONDS since '1970-01-01 00:00:00' UTC) as an unsigned integer

这篇关于如何将 MySQL 中的毫秒数与给定日期进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！