blocks|key|336810|text|使用TIMESTAMPDIFF+MySQL函数。例如，您可以使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|336811|SELECT+TIMESTAMPDIFF(SECOND,+'2012-06-06+13:13:55',+'2012-06-06+15:20:18')|code-block|syntax|javascript|336812|在本例中，TIMSTAMPDIFF函数的第三个参数是当前登录时间(NOW())。第二个参数是上次登录的时间，它已经在数据库中了。|336813|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff^0|2|D|2|D|0|0|0|5|C|X|5|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

USE <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff" rel="noreferrer"><code>TIMESTAMPDIFF</code></a> MySQL function. For example, you can use:

<pre><code>SELECT TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18')
</code></pre>

In your case, the third parameter of <code>TIMSTAMPDIFF</code> function would be the current login time (<code>NOW()</code>). Second parameter would be the last login time, which is already in the database.

blocks|key|336868|text|我对逻辑的两点看法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336869|语法为"old+date“-+:"new+date"，因此：|336870|SELECT+TIMESTAMPDIFF(SECOND,+'2018-11-15+15:00:00',+'2018-11-15+15:00:30')|code-block|syntax|javascript|336871|给30，|336872|SELECT+TIMESTAMPDIFF(SECOND,+'2018-11-15+15:00:55',+'2018-11-15+15:00:15')|336873|给予：-40|336874|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

my two cents about logic:

syntax is "old date" - :"new date", so:

<pre><code>SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:00', '2018-11-15 15:00:30')
</code></pre>

gives 30,

<pre><code>SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:55', '2018-11-15 15:00:15')
</code></pre>

gives:
-40

blocks|key|553279|text|如果开始日期时间和结束日期时间不同，请使用TIMEDIFF。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|553280|SELECT+TIMEDIFF(datetime1,datetime2)|code-block|syntax|javascript|553281|如果datetime1+>+datetime2，则|553282|SELECT+TIMEDIFF("2019-02-20+23:46:00","2019-02-19+23:45:00")|553283|给予:+24:01:00|553284|和datetime1+<+datetime2|553285|SELECT+TIMEDIFF("2019-02-19+23:45:00","2019-02-20+23:46:00")|553286|给出时间：-24:01:00|553287|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

If your start and end datetimes are on different days use TIMEDIFF.

<pre><code>SELECT TIMEDIFF(datetime1,datetime2)
</code></pre>

if datetime1 > datetime2 then

<pre><code>SELECT TIMEDIFF("2019-02-20 23:46:00","2019-02-19 23:45:00")
</code></pre>

gives: 24:01:00

and datetime1 &lt; datetime2 

<pre><code>SELECT TIMEDIFF("2019-02-19 23:45:00","2019-02-20 23:46:00")
</code></pre>

gives: -24:01:00

blocks|key|553367|text|我不认为被接受的答案是合适的。例如，如果上次登录时间和当前时间之间的差异是8小时，则以秒为单位的差异是不合逻辑的。正确的格式应为小时、分钟和秒。我对此进行了如下说明：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|553368|在这里，我创建了一个表login_info表来存储用户的登录信息。|offset|length|style|CODE|553369|CREATE+TABLE+login_info+(
++++->+user_id+INT+UNSIGNED+NOT+NULL+AUTO_INCREMENT,
++++->+last_login+DATETIME+NOT+NULL,
++++->+PRIMARY+KEY+(user_id)
++++->+);|code-block|syntax|javascript|553370|然后我用一些随机值填充表-|553371|INSERT+INTO+login_info+(last_login)+VALUES+
++++->+("2021-09-22+09:32:44"),
++++->+("2021-09-22+13:02:57"),
++++->+("2021-09-21+23:43:21"),
++++->+("2021-09-22+04:43:39"),
++++->+("2021-09-22+17:23:21");|553372|现在我计算last_login和current_time之间的差异如下：|553373|CREATE+TABLE+login_dur_in_sec+AS+
++++->+SELECT+user_id,
++++->+TIMESTAMPDIFF(SECOND,+last_login,+NOW())+AS+diff
++++->+FROM+login_info;|553374|SELECT+*+FROM+login_dur_in_sec;
%2B---------%2B-------%2B
%7C+user_id+%7C+diff++%7C
%2B---------%2B-------%2B
%7C+++++++1+%7C+28580+%7C
%7C+++++++2+%7C+15967+%7C
%7C+++++++3+%7C+63943+%7C
%7C+++++++4+%7C+45925+%7C
%7C+++++++5+%7C+++343+%7C
%2B---------%2B-------%2B|553375|CREATE+TABLE+hour_section+AS+
++++->+SELECT+user_id,
++++->+FLOOR+(diff+/+3600)+AS+hour_part+
++++->+FROM+login_dur_in_sec;|553376|CREATE+TABLE+minute_section+AS+
++++->+SELECT+user_id,+
++++->+FLOOR+(MOD+(diff,+3600)/+60)+AS+minute_part
++++->+FROM+login_dur_in_sec;|553377|CREATE+TABLE+second_section+AS
++++->+SELECT+user_id,
++++->+MOD+(MOD+(diff,+3600),+60)+AS+second_part
++++->+FROM+login_dur_in_sec;|553378|CREATE+TABLE+login_dur+AS+
++++->+SELECT+h.user_id,+h.hour_part,+m.minute_part,+s.second_part
++++->+FROM+hour_section+AS+h+INNER+JOIN+minute_section+AS+m
++++->+ON+h.user_id+=+m.user_id
++++->+INNER+JOIN+second_section+AS+s
++++->+ON+m.user_id+=+s.user_id;|553379|CREATE+TABLE+login_dur_trunc+AS+
++++->+SELECT+user_id,
++++->+CONCAT+(hour_part,+":",+minute_part,+":",+second_part)+AS+login_duration
++++->+FROM+login_dur;|553380|SELECT+*+FROM+login_dur_trunc;
%2B---------%2B----------------%2B
%7C+user_id+%7C+login_duration+%7C
%2B---------%2B----------------%2B
%7C+++++++1+%7C+8:14:46++++++++%7C
%7C+++++++2+%7C+4:44:33++++++++%7C
%7C+++++++3+%7C+18:4:9+++++++++%7C
%7C+++++++4+%7C+13:3:51++++++++%7C
%7C+++++++5+%7C+0:24:9+++++++++%7C
%2B---------%2B----------------%2B|553381|在这里，@Adi给出的答案并不总是像@CaiusJard所指出的那样有效。|553382|entityMap^0|0|B|A|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@$D|1E|E|1F|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1G|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1H|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1I|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1K|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|J|7|1L|8|@]|9|@]|A|$K|L]]|$1|W|3|X|5|J|7|1M|8|@]|9|@]|A|$K|L]]|$1|Y|3|Z|5|J|7|1N|8|@]|9|@]|A|$K|L]]|$1|10|3|11|5|J|7|1O|8|@]|9|@]|A|$K|L]]|$1|12|3|13|5|J|7|1P|8|@]|9|@]|A|$K|L]]|$1|14|3|15|5|J|7|1Q|8|@]|9|@]|A|$K|L]]|$1|16|3|17|5|J|7|1R|8|@]|9|@]|A|$K|L]]|$1|18|3|19|5|6|7|1S|8|@]|9|@]|A|$]]|$1|1A|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|1B|$]]

I don't think the accepted answer is appropriate. For example, if the difference between last login time and current time is 8 hours then getting the difference in seconds is illogical. The correct format will be in hours, minutes and seconds. I have illustrated this as follows -
Here, I create a table <code>login_info</code> table to store login information of users.
<pre><code>CREATE TABLE login_info (
 -&gt; user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 -&gt; last_login DATETIME NOT NULL,
 -&gt; PRIMARY KEY (user_id)
 -&gt; );
</code></pre>
Then I populate the table using some random values -
<pre><code>INSERT INTO login_info (last_login) VALUES 
 -&gt; (&quot;2021-09-22 09:32:44&quot;),
 -&gt; (&quot;2021-09-22 13:02:57&quot;),
 -&gt; (&quot;2021-09-21 23:43:21&quot;),
 -&gt; (&quot;2021-09-22 04:43:39&quot;),
 -&gt; (&quot;2021-09-22 17:23:21&quot;);
</code></pre>
Now I calculate the difference between last_login and current_time as follows:
<pre><code>CREATE TABLE login_dur_in_sec AS 
 -&gt; SELECT user_id,
 -&gt; TIMESTAMPDIFF(SECOND, last_login, NOW()) AS diff
 -&gt; FROM login_info;
</code></pre>
<pre><code>SELECT * FROM login_dur_in_sec;
+---------+-------+
| user_id | diff |
+---------+-------+
| 1 | 28580 |
| 2 | 15967 |
| 3 | 63943 |
| 4 | 45925 |
| 5 | 343 |
+---------+-------+
</code></pre>
<pre><code>CREATE TABLE hour_section AS 
 -&gt; SELECT user_id,
 -&gt; FLOOR (diff / 3600) AS hour_part 
 -&gt; FROM login_dur_in_sec;
</code></pre>
<pre><code>CREATE TABLE minute_section AS 
 -&gt; SELECT user_id, 
 -&gt; FLOOR (MOD (diff, 3600)/ 60) AS minute_part
 -&gt; FROM login_dur_in_sec;
</code></pre>
<pre><code>CREATE TABLE second_section AS
 -&gt; SELECT user_id,
 -&gt; MOD (MOD (diff, 3600), 60) AS second_part
 -&gt; FROM login_dur_in_sec;
</code></pre>
<pre><code>CREATE TABLE login_dur AS 
 -&gt; SELECT h.user_id, h.hour_part, m.minute_part, s.second_part
 -&gt; FROM hour_section AS h INNER JOIN minute_section AS m
 -&gt; ON h.user_id = m.user_id
 -&gt; INNER JOIN second_section AS s
 -&gt; ON m.user_id = s.user_id;
</code></pre>
<pre><code>CREATE TABLE login_dur_trunc AS 
 -&gt; SELECT user_id,
 -&gt; CONCAT (hour_part, &quot;:&quot;, minute_part, &quot;:&quot;, second_part) AS login_duration
 -&gt; FROM login_dur;
</code></pre>
<pre><code>SELECT * FROM login_dur_trunc;
+---------+----------------+
| user_id | login_duration |
+---------+----------------+
| 1 | 8:14:46 |
| 2 | 4:44:33 |
| 3 | 18:4:9 |
| 4 | 13:3:51 |
| 5 | 0:24:9 |
+---------+----------------+
</code></pre>
Here, the answer given by @Adi won't work always as pointed out by @CaiusJard.

I am storing the last login time in MySQL in, <code>datetime</code>-type filed. When users logs in, I want to get the difference between the last login time and the current time (which I get using <code>NOW()</code>).

How can I calculate it?

Calculate difference between two datetimes in MySQL

数据库

MySQL

我将上次登录的时间存储在datetime-type文件中的MySQL中。当用户登录时，我希望获得上次登录时间和当前时间(使用NOW()获得)之间的差值。我怎么计算呢？

问在MySQL中计算两个日期时间之间的差异
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中计算两个日期时间之间的差异EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中计算两个日期时间之间的差异
EN