MySQL如何在unix时间戳中获取时差 [英] MySQL How to get timedifference in unix timestamp

问题描述

我有一个保存值为 1506947452 的变量，需要使用公式从该日期中提取分钟数:started_data - now_date 但started_date 是 unix 时间戳格式 10 位整数...我以 ajax 形式收到并需要输入我尝试使用 mysql 查询:

I have variable that holds value 1506947452 and need to extract minutes from that date with formula: started_data - now_date but started_date is in unix timestamp format 10-digit int number...that i received in ajax form and need to put in mysql query i try with this:

SELECT TIMESTAMPDIFF(MINUTE, 1506947452, UNIX_TIMESTAMP());

但我从终端得到这个:

+-------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE, '1506947452', UNIX_TIMESTAMP()) |
+-------------------------------------------------------+
|                                                  NULL |
+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

那么我如何以 unix_timestamp 格式比较两个日期并获得分钟数?我需要使用 unix_timestamp 作为 start_date 所以其他日期格式不是解决方案.

So how i can compare two dates in unix_timestamp format and get minutes? I need to use unix_timestamp for start_date so others date format is not solution.

推荐答案

UNIX_TIMESTAMP 是自 UTC 时间 '1970-01-01 00:00:00' 以来的秒数.如果您有一个保存另一个 UNIX_TIMESTAMP 的变量，您可以获得差异并将秒转换为分钟(如果需要，还可以舍入/截断结果):

UNIX_TIMESTAMP is seconds seconds since '1970-01-01 00:00:00' in UTC. If you have a variable holding another UNIX_TIMESTAMP, you can get the difference and turn seconds to minutes (and round/truncate the result if needed):

 SELECT ROUND((UNIX_TIMESTAMP()-1506947452) / 60, 0)

这篇关于MySQL如何在unix时间戳中获取时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！