CODEWITHSUNDEEP
mysql
Aashick
Aashick

Reputation: 91

Mysql how to get exact day and time between two dates

I am passing date and time as input.

'2015-01-12 10:30:00' and '2015-01-13 11:30:00' like this. we can find the days, hours, minuets, seconds etc between the timestamps. by using the FUNCTION called timestampdiff().

I tried this query.

SELECT TIMESTAMPDIFF(HOUR,'2015-01-12 10:30:00','2015-01-13 11:30:00')/24 FROM DUAL

but it gives me output like 1.04.

My requirement is exact day and hours between that two dates.

Ex: '2015-01-12 10:30:00' and '2015-01-13 11:30:00' for this

OUTPUT : 1 day and 1 hrs.

thank you.

Upvotes: 2

Views: 261

Answers (2)

Amol Solanke
Amol Solanke

Reputation: 777

This query can give you required output

SELECT CONCAT(
TIMESTAMPDIFF(DAY,'2015-01-12 10:30:00', '2015-01-13 11:30:00'),
"day(s) and ",
TIMESTAMPDIFF(HOUR,'2015-01-12 10:30:00', '2015-01-13 11:30:00') % 24,
" hour(s)");

Upvotes: 0

Asenar
Asenar

Reputation: 7010

Just call that function twice. To have the result with the sentence you want, you can do something like this:

SELECT CONCAT(
  TIMESTAMPDIFF(DAY,'2015-01-12 10:30:00', '2015-01-13 11:30:00'), 
  " day(s) and ",
  TIMESTAMPDIFF(HOUR,'2015-01-12 10:30:00', '2015-01-13 11:30:00') % 24, 
  " hour(s)");

If you also want month or minute interval, just add one more function call and the appropriate modulo number

Upvotes: 2

Related Questions