CODEWITHSUNDEEP
mysqldatetime
JAMSHAID
JAMSHAID

Reputation: 1357

Difference between two DateTime values -MySql

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'))

Upvotes: 1

Views: 49

Answers (1)

Nick
Nick

Reputation: 147146

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

Upvotes: 1

Related Questions