我将最后登录时间以datetime
类型的字段存储在MySQL中。当用户登录时,我想获取最后登录时间和当前时间(使用NOW()
获取)之间的差异。
我该如何计算?
我将最后登录时间以datetime
类型的字段存储在MySQL中。当用户登录时,我想获取最后登录时间和当前时间(使用NOW()
获取)之间的差异。
我该如何计算?
使用MySQL函数TIMESTAMPDIFF
。例如,可以使用以下语句:
SELECT TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18')
在您的情况下,TIMESTAMPDIFF
函数的第三个参数应该是当前登录时间(NOW()
)。第二个参数应该是最后一次登录时间,已经在数据库中了。对于逻辑的看法:
语法是“旧日期”-“新日期”,因此:
SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:00', '2018-11-15 15:00:30')
给予30个,
SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:55', '2018-11-15 15:00:15')
给出:-40
SELECT TIMEDIFF(datetime1,datetime2)
如果 datetime1 大于 datetime2,那么
SELECT TIMEDIFF("2019-02-20 23:46:00","2019-02-19 23:45:00")
输出:24:01:00
并且 datetime1 小于 datetime2
SELECT TIMEDIFF("2019-02-19 23:45:00","2019-02-20 23:46:00")
给出的时间为:-24时01分00秒。
login_info
来存储用户的登录信息。CREATE TABLE login_info (
-> user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> last_login DATETIME NOT NULL,
-> PRIMARY KEY (user_id)
-> );
然后我使用一些随机值填充表格 -
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");
CREATE TABLE login_dur_in_sec AS
-> SELECT user_id,
-> TIMESTAMPDIFF(SECOND, last_login, NOW()) AS diff
-> FROM login_info;
SELECT * FROM login_dur_in_sec;
+---------+-------+
| user_id | diff |
+---------+-------+
| 1 | 28580 |
| 2 | 15967 |
| 3 | 63943 |
| 4 | 45925 |
| 5 | 343 |
+---------+-------+
CREATE TABLE hour_section AS
-> SELECT user_id,
-> FLOOR (diff / 3600) AS hour_part
-> FROM login_dur_in_sec;
CREATE TABLE minute_section AS
-> SELECT user_id,
-> FLOOR (MOD (diff, 3600)/ 60) AS minute_part
-> FROM login_dur_in_sec;
CREATE TABLE second_section AS
-> SELECT user_id,
-> MOD (MOD (diff, 3600), 60) AS second_part
-> FROM login_dur_in_sec;
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;
CREATE TABLE login_dur_trunc AS
-> SELECT user_id,
-> CONCAT (hour_part, ":", minute_part, ":", second_part) AS login_duration
-> FROM login_dur;
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 |
+---------+----------------+
这里,@Adi给出的答案并不总是有效的,正如@CaiusJard所指出的那样。