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分钟后的使用情况。
5 个解决方案
#1
54
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是datetime值，expr3只是一个时间值。
Check this link for more info.
查看这个链接获得更多信息。
#2
50
TIMESTAMPDIFF is 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).
将返回用户登录的分钟数(假设您将此类内容存储在这样的表中)。
#3
9
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)
#4
0
You can try and cast them to Unix Time stamp, and take the difference.
您可以尝试将它们转换为Unix时间戳，并采取不同的做法。
#5
0
SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn FROM LogTable
从LogTable中选择TIMESTAMPDIFF(MINUTE、LogOutTime、LogInTime)作为TimeLoggedIn
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.
这个例子如果使用毫微时间，将会破坏时间。所以对于计算毫秒数，我不推荐它，因为它输出的是负值。
#1
54
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是datetime值，expr3只是一个时间值。
Check this link for more info.
查看这个链接获得更多信息。
#2
50
TIMESTAMPDIFF is 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).
将返回用户登录的分钟数(假设您将此类内容存储在这样的表中)。
#3
9
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)
#4
0
You can try and cast them to Unix Time stamp, and take the difference.
您可以尝试将它们转换为Unix时间戳，并采取不同的做法。
#5
0
SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn FROM LogTable
从LogTable中选择TIMESTAMPDIFF(MINUTE、LogOutTime、LogInTime)作为TimeLoggedIn
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.
这个例子如果使用毫微时间，将会破坏时间。所以对于计算毫秒数，我不推荐它，因为它输出的是负值。