How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & Iā€™ll get back to you as soon as possible.

Method 1

There are functions TIMEDIFF(expr1,expr2), which returns the value of expr1-expr2, and TIME_TO_SEC(expr3), which expresses expr3 in seconds.

Note that expr1 and expr2 are datetime values, and expr3 is a time value only.

Check this link for more info.

Method 2

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).

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).

Method 3

I would do it like this ā€“ fetch where last activity is within 10 mins of now

SELECT * FROM table WHERE last_activity >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

Method 4

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.

Method 5

You can try and cast them to Unix Time stamp, and take the difference.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x