Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal StudiesMySQL 如何转换 timediff 输出为天、小时、分钟、秒格式？
要了解 MySQL 将 timediff 输出转换为天、小时、分钟和秒的格式，你需要使用 MySQL 中的 CONCAT()。
让我们创建一个表。创建表的查询如下
mysql> create table convertTimeDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartDate datetime, -> EndDate datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)
使用 insert 命令在表中插入一些记录。插入记录的查询如下
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values(date_add(now(),interval -3 hour),date_add(now(),interval 3 hour));
Query OK, 1 row affected (0.41 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values(date_add(now(),interval -2 hour),date_add(now(),interval 2 hour));
Query OK, 1 row affected (0.27 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values('2018-04-05 12:30:35','2018-05-17 14:30:50');
Query OK, 1 row affected (0.14 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values('2017-10-11 11:20:30','2017-12-17 15:21:55');
Query OK, 1 row affected (0.20 sec)使用 select 语句显示表中的所有记录。查询如下
mysql> select *from convertTimeDifferenceDemo;
以下是输出
+----+---------------------+---------------------+ | Id | StartDate | EndDate | +----+---------------------+---------------------+ | 1 | 2019-01-28 20:55:33 | 2019-01-29 02:55:33 | | 2 | 2019-01-28 21:57:42 | 2019-01-29 01:57:42 | | 3 | 2018-04-05 12:30:35 | 2018-05-17 14:30:50 | | 4 | 2017-10-11 11:20:30 | 2017-12-17 15:21:55 | +----+---------------------+---------------------+ 4 rows in set (0.00 sec)
以下是将 timediff 输出转换为天、小时、分钟和秒格式的查询
mysql> SELECT CONCAT( -> FLOOR(HOUR(TIMEDIFF(StartDate,EndDate)) / 24), ' DAYS ', -> MOD(HOUR(TIMEDIFF(StartDate,EndDate)), 24), ' HOURS ', -> MINUTE(TIMEDIFF(StartDate,EndDate)), ' MINUTES ') AS DESCRIPTION -> FROM convertTimeDifferenceDemo;
以下是输出
+------------------------------+ | DESCRIPTION | +------------------------------+ | 0 DAYS 6 HOURS 0 MINUTES | | 0 DAYS 4 HOURS 0 MINUTES | | 34 DAYS 22 HOURS 59 MINUTES | | 34 DAYS 22 HOURS 59 MINUTES | +------------------------------+ 4 rows in set, 6 warnings (0.04 sec)
更新于：2019 年 7 月 30 日
1K+ 浏览
广告