MySQL时间格式转换解析 13位时间戳转日期的高效方法

我记得有一次我在一个项目中处理日志数据，这些日志存储有 1 3 位毫秒时间戳。
我需要将这些时间戳转换为人类可读的日期格式以进行解析。
当时我使用的是这样的SQL代码：
SELECT FROM_UNIXTIME(timestamp_ms/1 000) AS Converted_datetime FROM 日志；
这段代码效果很好：它将 1 3 位时间戳除以 1 000，然后使用 FROM_UNIXTIME 函数将其转换为日期时间格式。
但是，我注意到如果时间戳非常大，例如接近9 9 9 9 9 9 9 9 9 9 9 9 9 ，转换结果将不正确，甚至返回NULL。

后来网上查了一些资料，发现如果想在WHERE子句中使用这种转换，就会导致索引失败。
例如，如果你直接写：
从日志中选择“2 02 3 -03 -1 5 00:00:00”和“2 02 3 -03 -1 5 2 3 :5 9 :5 9 ”之间的 WHERE FROM_UNIXTIME(timestamp_ms/1 000);
此查询不会利用索引，因为它使用索引列上的函数。
为了解决这个问题，我使用了预先计算的时间戳范围：
设置@start_ts_ms = UNIX_TIMESTAMP('2 02 3 -03 -1 5 00:00:00') 1 000; SET @end_ts_ms = UNIX_TIMESTAMP('2 02 3 -03 -1 5 2 3 :5 9 :5 9 ') 1 000; 从日志中选择，其中 timestamp_ms > @start_ts_ms AND timestamp_ms < @end_ts_ms;
这提高了查询效率。
我突然想到的另一件事是，如果日志数据量很大，那么使用BIGINT来存储毫秒时间戳会更高效，因为它可以减少存储空间并提高查询速度。

那么，如果有一天你面临一个需要同时处理毫秒级时间戳和解析日期维度的项目，你会如何处理？

戳实现两个时间戳相减获取时间间隔的MySQL方法mysql中两个时间

直接使用 TIMESTAMPDIFF()。
单位包括秒、分钟、小时、天等。

datetime1 晚于 time2 ，效果为负。

或者 UNIX_TIMESTAMP() 来转换第二个差值。

按照适当的秒单位来划分。

第二个差别，3 6 00=小时差别。

点差/6 0=小时差。

直接TIMESTAMPDIFF()是最直接的。

MySQL日期格式转换 13位时间戳转YYYY-MM-DD的三种方案

嘿，我需要和你谈谈这件事。
我以前做过很多次，也遇到过很多陷阱。
去年，我正在做一个电子商务项目，继承了一个旧系统，该系统运行了大量 1 3 位毫秒时间戳。
起初我惊慌失措并尝试了一切。

我基本上使用了我提到的所有解决方案：
1 DATE_FORMAT + FROM_UNIXTIME，这个对象确实是可变的。
例如，如果您想查看特定日期下了多少订单，您可以使用以下命令： 广场 SELECT DATE_FORMAT(FROM_UNIXTIME(your_1 3 _digit_column / 1 000), '%Y-%m-%d') AS FORMAT DATE, COUNT() AS ORDERS 从订单 组 on_date 它非常容易使用。
如果需要，您可以查看月份或星期，并且可以根据需要更改格式。
代码有点长。
2 、DATE(FROM_UNIXTIME(...))，这是最简单也是最残酷的方法。
它什么时候发生的 它上线了一年零一个月。
广场 选择日期(FROM_UNIXTIME(your_1 3 _digit_column / 1 000)) AS date_only 从日志中 例如，如果您想检查特定用户何时登录，您只需要日期，而不是时间。
这个方法就完成了。
代码也很短。
3 . CAST/CONVERT，这个方法明显是转换的，看起来很明显。
有时，当与其他表匹配时，日期类型不匹配，因此您必须更改它： 广场 CAST(FROM_UNIXTIME(your_1 3 _digit_column / 1 000) AS DATE) 选择发布日期 来自事件 比如我之前接手了一个报表系统，其他的表都是DATE类型，所以时间戳字段就得这样改。
没有错误。

但是，有一些陷阱需要注意：
数据类型！去年我就差点摔倒在这里。
1 3 位时间戳必须存储在BIGINT中，不要存储INT，会爆炸。
最大INT超过2 亿，1 3 位时间戳可以超过这个数字。
另外，如果直接将它们设置为 DATETIME，毫秒将会丢失。
这件事你应该想清楚。
后来我在一些场景中使用了DATETIME(3 )，保留毫秒精度并简化解析。
时区！真让人头疼啊。
我记得有一次数据是UTC的，我的服务器在东八区，我直接使用FROM_UNIXTIME，但是输出的时间全错了。
之后，CONVERT_TZ 来拯救： 广场 SELECT CONVERT_TZ(FROM_UNIXTIME(your_1 3 _digit_column/1 000), '+00:00', '+08 :00') AS 当地时间 这一步不能跳过。
准确性！直接除以1 000并保存DATETIME。
几毫秒过去了。
如果您需要毫秒，则应该使用 DATETIME(3 ) 或在应用程序层处理它。

后来我意识到1 3 位时间戳在数据分析中很有用：
时间序列分析：例如我分析了去年的用户活动，并按天分组，看趋势。
使用 DATE_FORMAT 提取月份，这也可以方便地查看当前更改： 广场 SELECT DATE_FORMAT(FROM_UNIXTIME(ts/1 000), '%Y-%m') AS MONTH, COUNT() AS USERS 从运动中 每月组数 时间间隔：要计算用户存在的时间，只需减去两个时间戳即可，这比字符串日期更方便。
我计算了订单处理时间，以秒为单位的差异很明显： 广场 SELECT TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(start_ts/1 000), FROM_UNIXTIME(end_ts/1 000)) AS 持续时间 从订单 范围查询：查询2 02 3 年1 0月到上一年的数据，直接从日期范围使用时间戳范围速度更快。
广场 在 UNIX_TIMESTAMP('2 02 3 -1 0-01 ') 和 UNIX_TIMESTAMP('2 02 3 -1 0-3 1 2 3 :5 9 :5 9 ') FROM ORDER BY 之间选择 your_1 3 _digit_column / 1 000
一般来说，您提到的所有解决方案都是可靠的。
我通常根据自己的需要来选择：
如果我需要格式化字符串，我使用DATE_FORMAT。
只要日期，不用担心时间，DATE 就可以了。
如果类型不匹配，请使用 CAST/CONVERT。

记住这些陷阱：数据类型、时区、准确性。
一旦理解了这些，1 3 位时间戳就会很容易使用。
扩展应用方面，时间序列分析、区间计算、范围查询等方面表现出色。

MySQL获取当前时间和时间戳

哎哟，说实话，我用MySQL的时候，这些时间函数真是救命稻草啊！记得有一次帮朋友的公司建数据库，需要记录用户的登录时间。
当时我用NOW()将时间戳直接存到数据库中，方便快捷。
当时是 2 01 9 年，我们的服务器在中国，所以时间以中国时区为准。

有一次他们让我帮忙解决一个跨时区的问题。
我当时想我必须使用UNIX_TIMESTAMP()，这将使计算时间差变得更加容易。
我记得有一个用户在纽约，我们在北京。
他们需要知道用户登录后在北京呆了多久。
我用UNIX_TIMESTAMP()减去用户登录时间戳，结果一目了然。

还有一次，我正在帮助一个电子商务网站处理订单。
您只需记录订单日期，而不是确切时间。
我使用CURDATE()，它简单粗暴。
它直接捕获日期并节省大量存储空间。
到了2 02 0年，他们的订单量达到了每天数万。
使用 CURDATE() 确实为他们省去了很多麻烦。

但是，使用这些功能时还应该注意时区问题。
我记得有一次一位同事从美国出差回来。
他使用的计算机位于美国时区。
因此，当他查看数据库时，发现时间不对。
我告诉他使用 NOW() 时要注意时区，否则日期会弄乱。

哎，说实话，数据库里的细节还真不少。
但是，如果使用正确的方法，这是非常方便的。
正如我之前所说，根据您的需求选择功能可以为您省去很多麻烦。