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 Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to convert a MySQL TIME value to days and hours form?
Here, we are converting time value, for example 150:50:10 to days and hours form, i.e. 6 days 6 hours.
You can use CONCAT() along with HOUR() for this. Let us first create a table −
mysql> create table DemoTable657(DueTime time); Query OK, 0 rows affected (3.68 sec)
Insert some records in the table using insert command. Here, we have inserted the records in the form of total hours −
mysql> insert into DemoTable657 values('120:30:00');
Query OK, 1 row affected (0.38 sec)
mysql> insert into DemoTable657 values('150:50:10');
Query OK, 1 row affected (0.27 sec)Display all records from the table using select statement −
mysql> select *from DemoTable657;
This will produce the following output −
+-----------+ | DueTime | +-----------+ | 120:30:00 | | 150:50:10 | +-----------+ 2 rows in set (0.00 sec)
Here is the query to convert TIME value to days hours form −
mysql> SELECT CONCAT( FLOOR(HOUR(DueTime)/24),' DAYS, ', HOUR(DueTime) MOD 24, ' HOURS.') from DemoTable657;
This will produce the following output −
+-----------------------------------------------------------------------------+ | CONCAT( FLOOR(HOUR(DueTime)/24),' DAYS, ', HOUR(DueTime) MOD 24, ' HOURS.') | +-----------------------------------------------------------------------------+ | 5 DAYS, 0 HOURS. | | 6 DAYS, 6 HOURS. | +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Updated on: 23-Aug-2019
497 Views
Advertisements