CODEWITHSUNDEEP
phpmysqltimedate-format
a coder
a coder

Reputation: 7649

MySQL TIME stamp to days, hours

In looking at the MySQL documentation, I am not finding an option under DATE_FORMAT (referenced by TIME_FORMAT) for converting a MySQL TIME value (ex: 172:04:11) to something like, "7 days, 4 hours".

Is there a way (in MySQL) to format time in this manner? Or should I just operate on the returned TIME value in PHP?

Upvotes: 3

Views: 2572

Answers (3)

a coder
a coder

Reputation: 7649

I hate having to answer my own question, but to clear things up MySQL's TIME() function truncates values over 838:59:59 - obviously not desirable.

The solution I found was to use this:

TIMESTAMPDIFF(SECOND,date_old,date_newer)

Which does not have the same truncation problem. Once you have the seconds use some server side scripting to format the time as in my original request. Reply if you want to see an example in PHP, or write your own. :)

Hope this is helpful?

Upvotes: 1

phsaires
phsaires

Reputation: 2378

SELECT CONCAT( FLOOR(TIME_FORMAT('172:04:11', '%H')/24), ' days ', (TIME_FORMAT('172:04:11', '%H') MOD 24),   ' hours ', TIME_FORMAT('172:04:11', '%i'), ' minutes ', TIME_FORMAT('172:04:11', '%s'), ' seconds')

Upvotes: 0

piotrm
piotrm

Reputation: 12356

A variable of TIME type would be most probably used to represent a time interval, and you can use addtime() function to add this interval to a given DATETIME type variable.

SELECT ADDTIME( NOW(), '172:04:11' );

works as expected, however if you need to display this time interval as days and hours simply use:

SELECT CONCAT( 
  FLOOR(HOUR('172:04:11')/24), 
  ' days, ',
  HOUR('172:04:11') MOD 24, 
  ' hours.');

Upvotes: 0

Related Questions