How can I get the difference between two timestamps in days? Should I be using a datetime column for this?
我怎样才能在几天内得到两个时间戳之间的区别呢?我应该为此使用datetime列吗?
I switched my column to datetime. Simple subtraction doesn't seem to give me a result in days.
mysql> SELECT NOW(), last_confirmation_attempt, NOW() - last_confirmation_attempt AS diff FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+-----------------+
| NOW() | last_confirmation_attempt | diff |
+---------------------+---------------------------+-----------------+
| 2010-03-30 10:52:31 | 2010-03-16 10:41:47 | 14001084.000000 |
+---------------------+---------------------------+-----------------+
1 row in set (0.00 sec)
I don't think diff
is in seconds, because when I divide diff
by number of seconds in a day ( 86,400 ), I don't get a sensical answer:
我不认为diff是用秒来表示的,因为当我用diff除以一天的秒数(86,400)时,我得不到一个合理的答案:
mysql> SELECT NOW(), last_confirmation_attempt, ( NOW() - last_confirmation_attempt) / 86400 AS diff FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+----------------+
| NOW() | last_confirmation_attempt | diff |
+---------------------+---------------------------+----------------+
| 2010-03-30 10:58:58 | 2010-03-16 10:41:47 | 162.0568402778 |
+---------------------+---------------------------+----------------+
1 row in set (0.00 sec)
7 个解决方案
#1
94
If you're happy to ignore the time portion in the columns, DATEDIFF() will give you the difference you're looking for in days.
如果您乐于忽略列中的时间部分,DATEDIFF()将在几天内给您带来您想要的差异。
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
| 17 |
+------+
#2
11
I know is quite old, but I'll say just for the sake of it - I was looking for the same problem and got here, but I needed the difference in days.
我知道这是一个很老的问题,但我想说的是为了这个原因——我一直在寻找同样的问题,然后来到了这里,但我需要几天的时间来解决这个问题。
I used SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24
Unix_timestamp returns the difference in seconds, and then I just divide into minutes(seconds/60), hours(minutes/60), days(hours/24).
我使用了SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24 UNIX_TIMESTAMP以秒为单位返回差值,然后我将其划分为分钟(秒/60)、小时(分钟/60)、天数(小时/24小时)。
#3
5
If you want to return in full TIMESTAMP format than try it: -
如果你想返回全时间戳格式,请尝试:-
SELECT TIMEDIFF(`call_end_time`, `call_start_time`) as diff from tablename;
return like
还喜欢
diff
- - -
00:05:15
#4
4
CREATE TABLE t (d1 timestamp, d2 timestamp);
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 05:00:00');
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-11 00:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-04-01 13:00:00');
SELECT d2, d1, DATEDIFF(d2, d1) AS diff FROM t;
+---------------------+---------------------+------+
| d2 | d1 | diff |
+---------------------+---------------------+------+
| 2010-03-30 05:00:00 | 2010-03-11 12:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-11 12:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-11 00:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-10 12:00:00 | 20 |
| 2010-04-01 13:00:00 | 2010-03-10 12:00:00 | 22 |
+---------------------+---------------------+------+
5 rows in set (0.00 sec)
#5
3
SELECT DATEDIFF( now(), '2013-06-20' );
选择DATEDIFF(now(),“2013-06-20”);
here datediff takes two arguments 'upto-date', 'from-date'
这里datediff使用两个参数“最新的”、“最新的”
What i have done is, using now() function, i can get no. of days since 20-june-2013 till today.
我所做的是,使用now()函数,我可以得到no。从2013年6月20日到今天。
#6
3
If you need the difference in days accounting up to the second:
如果你需要以天为单位的差额计算到第二天:
SELECT TIMESTAMPDIFF(SECOND,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400 AS diff
选择TIMESTAMPDIFF(第二,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400作为diff。
will return 16.8629 days.
将返回16.8629天。
#7
0
SELECT DATEDIFF(max_date, min_date) as days from my table. This works even if the col max_date
and min_date
are in string data types.
从表中选择DATEDIFF(max_date, min_date)作为天数。即使col max_date和min_date是字符串数据类型,这也可以工作。
#1
94
If you're happy to ignore the time portion in the columns, DATEDIFF() will give you the difference you're looking for in days.
如果您乐于忽略列中的时间部分,DATEDIFF()将在几天内给您带来您想要的差异。
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
| 17 |
+------+
#2
11
I know is quite old, but I'll say just for the sake of it - I was looking for the same problem and got here, but I needed the difference in days.
我知道这是一个很老的问题,但我想说的是为了这个原因——我一直在寻找同样的问题,然后来到了这里,但我需要几天的时间来解决这个问题。
I used SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24
Unix_timestamp returns the difference in seconds, and then I just divide into minutes(seconds/60), hours(minutes/60), days(hours/24).
我使用了SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24 UNIX_TIMESTAMP以秒为单位返回差值,然后我将其划分为分钟(秒/60)、小时(分钟/60)、天数(小时/24小时)。
#3
5
If you want to return in full TIMESTAMP format than try it: -
如果你想返回全时间戳格式,请尝试:-
SELECT TIMEDIFF(`call_end_time`, `call_start_time`) as diff from tablename;
return like
还喜欢
diff
- - -
00:05:15
#4
4
CREATE TABLE t (d1 timestamp, d2 timestamp);
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 05:00:00');
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-11 00:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-04-01 13:00:00');
SELECT d2, d1, DATEDIFF(d2, d1) AS diff FROM t;
+---------------------+---------------------+------+
| d2 | d1 | diff |
+---------------------+---------------------+------+
| 2010-03-30 05:00:00 | 2010-03-11 12:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-11 12:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-11 00:00:00 | 19 |
| 2010-03-30 13:00:00 | 2010-03-10 12:00:00 | 20 |
| 2010-04-01 13:00:00 | 2010-03-10 12:00:00 | 22 |
+---------------------+---------------------+------+
5 rows in set (0.00 sec)
#5
3
SELECT DATEDIFF( now(), '2013-06-20' );
选择DATEDIFF(now(),“2013-06-20”);
here datediff takes two arguments 'upto-date', 'from-date'
这里datediff使用两个参数“最新的”、“最新的”
What i have done is, using now() function, i can get no. of days since 20-june-2013 till today.
我所做的是,使用now()函数,我可以得到no。从2013年6月20日到今天。
#6
3
If you need the difference in days accounting up to the second:
如果你需要以天为单位的差额计算到第二天:
SELECT TIMESTAMPDIFF(SECOND,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400 AS diff
选择TIMESTAMPDIFF(第二,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400作为diff。
will return 16.8629 days.
将返回16.8629天。
#7
0
SELECT DATEDIFF(max_date, min_date) as days from my table. This works even if the col max_date
and min_date
are in string data types.
从表中选择DATEDIFF(max_date, min_date)作为天数。即使col max_date和min_date是字符串数据类型,这也可以工作。