长度为13个字符的SQL语句 [英] SQL statement for a 13 character long timestamp

问题描述

我有一个封闭的源应用程序，该应用程序在MySQL数据库中放置了13个字符的长时间戳.一个值例如:

I have a closed source application which put a 13 character long timestamp in a MySQL database. One value is for example:

1277953190942

现在我遇到了问题，我必须编写一条sql语句，该语句将返回与特殊日子匹配的表的所有结果.

Now I have the problem, that I have to write a sql statement which will return me all results of a table which match a special day.

例如，我有2010年7月1日，我将获得时间在2010年7月1日00:00:00到01月7月23:59:59之间的所有行.

So I have for example 01. July 2010 and I will get all rows where the time is in between 01. July 2010 00:00:00 until 01. July 23:59:59.

我该如何编写此sql语句?

How do I have to write this sql statement?

select * from myTable where TIMESTAMP = ???

有人知道吗?

最好的问候.

推荐答案

这是 Unix时间戳毫秒精度，即距 Unix时代以来的毫秒数.因此正确的说法将是

This is a Unix timestamp with millisecond precision, i.e. the number of milliseconds since Unix epoch. Hence the correct statement would be

select * from myTable where DATE(FROM_UNIXTIME(timestamp / 1000)) = DATE('2010-07-01');

请注意，查询不会很快进行，因为表中的每个值都必须转换为日期才能进行比较.一旦该查询开始出现问题(而不是早于第二个问题)，您可能希望在一天的开始和结束时使用使用2个时间戳的方法，而这并不需要太多的转换开销.我会把它留给热心的人;)

Note that the query won't be fast es every value in your table will have to be converted to a date for comparison. As soon as this query starts to make problems (and not a second earlier), you may want to use an approach using 2 timestamps for the beginning and end of day which wouldn't require much conversion overhead. I'll leave this as an exercise to the eager though ;)

这篇关于长度为13个字符的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！