Could you please help.
In the database time represent in string format like 5.5 (hours) how to convert to 5:30 min
I tried
select CAST('5.50' AS DATETIME)
select STR_TO_DATE('5.50', '%h:%i %p') ;
but this is not working.
Thank you
Add a comment
|
3 Answers
I don't know of a neat way to do this but here is a hacky way off the top of my head.
select SEC_TO_TIME(5.5 * 3600)
I'm converting 5.5 into seconds then from seconds to a time.
Hope this helps
Comments
You can add the 5.5 hours interval to time 00:00:00 to get a time of 05:30:00.
This however:
select time '00:00:00' + interval '5.5' hour
does not work. MySQL truncates the 5.5 to 5 for some reason. So use
select time '00:00:00' + interval '5.5' * 60 * 60 second
instead.
UPDATE: Just looked it up. That interval 5.5 hour does not work was filed as a bug in 2007 here: https://bugs.mysql.com/bug.php?id=31013.
Comments
To get the time in format hh:mm:ss you could use this query.
SELECT SUBTIME(TIME(DATE_ADD(FROM_UNIXTIME(UNIX_TIMESTAMP(0),'%Y-%m-%d %H:%i:%s'), INTERVAL 5.5*60 MINUTE)), TIME(UNIX_TIMESTAMP(0)));
check it out on SQL fiddle
EDIT
In case you want seconds accuracy then change the query like this.
SELECT SUBTIME(TIME(DATE_ADD(FROM_UNIXTIME(UNIX_TIMESTAMP(0),'%Y-%m-%d %H:%i:%s'), INTERVAL 5.501*3600 SECOND)), TIME(UNIX_TIMESTAMP(0)));
1 Comment
Green_Crocodile
Thank you what about if time more then 24 hours?