Question

MySQL convert timediff output to day, hour, minute, second format?

Answer 1

To understand the MySQL convert timediff output to day, hour, minute, and second format, you need to use CONCAT() from MySQL.

Let us create a table. The query to create a table is as follows:

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 some records in the table using insert command. The query to insert record is as follows:

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)

Display all records from the table using select statement.

Answer 2

The quickest way to see a list of columns for a table is to use DESCRIBE.
DESCRIBE [table name]
SHOW COLUMNS FROM [table name]
SHOW COLUMNS FROM Customer FROM ShoppingCart LIKE 'user_%'
SELECT COLUMN_NAME FROM information_schema. ...
SELECT COLUMN_NAME FROM information_schema.