如何在两个日期时间值之间减去在 MYSQL 中使用 SQL 并以分钟或秒为单位检索结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/981262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?
提问by Ali
I want to subtract between two date time values using SQL in MySQL such that I get the interval in minutes or seconds. Any ideas? I want to run a SQL query that retrieves uses from a database who have logged in like 10 minutes from the time.
我想在 MySQL 中使用 SQL 在两个日期时间值之间减去，这样我就可以得到以分钟或秒为单位的间隔。有任何想法吗？我想运行一个 SQL 查询，该查询从大约 10 分钟后登录的数据库中检索使用情况。
回答by Matthew Jones
There are functions TIMEDIFF(expr1,expr2), which returns the value of expr1-expr2, and TIME_TO_SEC(expr3), which expresses expr1 in seconds.
有函数 TIMEDIFF(expr1,expr2)，它返回 expr1-expr2 的值，和 TIME_TO_SEC(expr3)，它以秒表示 expr1。
Note that expr1 and expr2 are datetime values, and expr3 is a time value only.
请注意， expr1 和 expr2 是日期时间值，而 expr3 仅是时间值。
Check this linkfor more info.
查看此链接以获取更多信息。
回答by Welbog
TIMESTAMPDIFFis like TIMEDIFF which Matthew states, except it returns the difference of the two datetimes in whatever units you desire (seconds, minutes, hours, etc).
TIMESTAMPDIFF就像马修所说的 TIMEDIFF，除了它以您想要的任何单位（秒、分钟、小时等）返回两个日期时间的差异。
For example,
例如，
SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn
FROM LogTable
Would return the number of minutes the user was logged in (assuming you stored this kind of thing in a table like that).
将返回用户登录的分钟数（假设您将此类内容存储在这样的表中）。
回答by David Snabel-Caunt
I would do it like this - fetch where last activity is within 10 mins of now
我会这样做 - 获取上次活动在现在 10 分钟内的位置
SELECT * FROM table WHERE last_activity >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
回答by David Snabel-Caunt
SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn
FROM LogTable
This example shall ruin the time if its used by using millitary time. So for calculating millitairy time I do not recommend it Because it outputs negative values.
如果使用毫秒时间，这个例子会破坏时间。所以对于计算毫秒时间我不推荐它，因为它输出负值。
回答by Andrew Whittington
You can try and cast them to Unix Time stamp, and take the difference.
您可以尝试将它们转换为 Unix 时间戳，并从中获取差异。
