Syntax error Get the time difference and convert it to hours in MySQL?

Get the time difference and convert it to hours in MySQL?

You can achieve with the help of timestampdiff() method from MySQL. The syntax is as follows −

Syntax

SELECT ABS(TIMESTAMPDIFF(HOUR,yourColumnName1,yourColumnName2)) as
anyVariableName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table DifferenceInHours
   -> (
   -> StartDateTime datetime,
   -> EndDateTime datetime
   -> );
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into DifferenceInHours values('2018-12-20 10:00:00', '2018-12-19 12:00:00');
Query OK, 1 row affected (0.11 sec)

mysql> insert into DifferenceInHours values('2018-12-20 11:00:00', '2018-12-19 11:00:00');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DifferenceInHours values('2018-12-20 10:30:00', '2018-12-19 11:00:00');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from DifferenceInHours;

Output

+---------------------+---------------------+
| StartDateTime       | EndDateTime         |
+---------------------+---------------------+
| 2018-12-20 10:00:00 | 2018-12-19 12:00:00 |
| 2018-12-20 11:00:00 | 2018-12-19 11:00:00 |
| 2018-12-20 10:30:00 | 2018-12-19 11:00:00 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Here is the query that gets time difference in hours. The query is as follows −

mysql> SELECT ABS(TIMESTAMPDIFF(HOUR,StartDateTime,EndDateTime)) as Hour from
DifferenceInHours;

The following is the output displaying the time difference in hours −

+------+
| Hour |
+------+
|   22 |
|   24 |
|   23 |
+------+
3 rows in set (0.00 sec)
Updated on: 2020-06-25T11:11:26+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements