MySQL将秒转换为日期时间

Question

我有一个整数'63605914755'，如下所示：

SELECT TO_SECONDS( '2015-08-04 13:40:56' )
-----
Result: '63605914755'

如何将“63605914755”从秒转换回日期时间（例如：“2015-08-04 13:40:56”）？

- Maxime

---

请阅读此链接：https://dev59.com/01XTa4cB1Zd3GeqP1nHg，其中包含有关将秒或毫秒转换为时间戳或字符串的信息。 - Neil

我已经阅读了它，但它没有解决我的问题。这就是为什么我做了这个。 UNIX_TIMESTAMP(63605914755)和FROM_UNIXTIME(63605914755)都返回NULL。 - Maxime

我建议使用这个 - 然后希望它能正常工作。https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp - Neil

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_makedate - Ivijan Stefan Stipić

Answer 1

SELECT
  from_unixtime(seconds - to_seconds('1970-01-01 00:00:00')) as my_date_time
FROM 
  your_table

这里有一个SQLFiddle。

- Mihai Matei

---

'63605914755' 是秒数，而不是 UNIX 时间戳。 您的解决方案返回了 NULL。 - Maxime

如果您的MySQL服务器具有时区偏移量，则应当小心，这将导致FROM_UNIXTIME的结果出现偏差。MySQL默认使用的时区是SYSTEM，但可以更改。 - Will B.

Answer 2

另一种方法是将秒数添加回0000-01-00基准日期。然而，使用0000-01-00会导致NULL，因此我们必须指定月份的第一天0000-01-01。
这将导致日期偏移1天。为了解决这个问题，我们只需从结果日期中减去1天：

示例：https://dbfiddle.uk/YNJ7Q856

SELECT '0000-01-01' + INTERVAL TO_SECONDS('2015-08-04 13:40:56') SECOND - INTERVAL 1 DAY;

结果：

2015-08-04 13:40:56

---

创建一个 FROM_SECONDS 函数，使用以下代码：

DELIMITER //
CREATE FUNCTION `FROM_SECONDS`(
    `secs` BIGINT
)
RETURNS DATETIME
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    RETURN '0000-01-01' + INTERVAL secs SECOND - INTERVAL 1 DAY;
END//

SELECT FROM_SECONDS(TO_SECONDS('2015-08-04 13:40:46'));

由于 DATETIME 值是时间参考点（静态），所以上述查询可以是 DETERMINISTIC 的，因为提供的秒数的结果值始终相同。

---

注意：
由于Unix纪元日期基于UTC，当您的MySQL服务器使用具有偏移量的时区时，使用FROM_UNIXTIME（秒-纪元秒），所得到的日期时间将会偏移该偏移量。MySQL使用的默认时区是“SYSTEM”时区。
对于美国东部标准时间（EST），使用FROM_UNIXTIME将得到2015-08-04 09:40:56。

为解决此问题，您需要计算UTC_TIMESTAMP和CURRENT_TIME之间的分钟差异，并从FROM_UNIXTIME中减去结果分钟偏移量。如果您的服务器使用UTC，则这将不会产生影响，因为结果偏移量为0。

SELECT FROM_UNIXTIME(TO_SECONDS('2015-08-04 13:40:56') - TO_SECONDS('1970-01-01 00:00:00')) - INTERVAL TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), NOW()) MINUTE;

结果：

2015-08-04 13:40:56

- Will B.

---

该死，这很丑陋但正确，而单位时间限制并不限制它。令人烦恼的是 MySQL 拥有一个不完整的函数集。它提供了 to_seconds 函数，这是一个很棒的函数，但它没有提供相反的函数。这绝非 MySQL 功能不完整的唯一示例。 - user9526573