Difference between two DateTime values -MySql

41
January 13, 2019, at 08:10 AM

I am working on an application where I need to get the difference between two columns having data type as DateTime and if the difference is less than one hour, it should give 1 and if greater than 1 and less than 2, it should return 2 and so on. I have been trying different queries but none of them looks working quite well.

If I need to do something in coding, please suggest me that as well. I am using Java to design the application. For what I tried is

SELECT floor((TIMEDIFF('2019-01-08 18:23:13', '2019-01-08 18:40:36'))) AS DIFF
SELECT floor((DATEDIFF('2019-01-08 18:23:13', '2019-01-08 18:40:36'))) AS DIFF
SELECT floor(TIMEDIFF(hour,'2019-01-08 18:23:13', '2019-01-08 18:40:36'))
Answer 1

Use TIMESTAMPDIFF, which will take the floor automatically, and then add one:

SELECT  TIMESTAMPDIFF(HOUR, '2019-01-08 18:23:13', '2019-01-08 18:40:36')+1

Output

1

Demo on dbfiddle

READ ALSO
YII 1.1 - How to fetch multiple record in FETCH_KEY_PAIR format

YII 1.1 - How to fetch multiple record in FETCH_KEY_PAIR format

We are facing some problem for fetch record in PDO formatSee below my example code:

24
SQL Statement loads very slow with Order By

SQL Statement loads very slow with Order By

I have this sql statement below and returns in 0149seconds:

19
Python mysql connector query same result every time

Python mysql connector query same result every time

I want to access a mysql database via mysqlconnector in python 2

44