CODEWITHSUNDEEP
mysql
Green_Crocodile
Green_Crocodile

Reputation: 33

MySQL String to Time Format

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

Upvotes: 0

Views: 3140

Answers (3)

user2560539
user2560539

Reputation:

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

Upvotes: 0

Andrew Bone
Andrew Bone

Reputation: 7291

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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.

Upvotes: 1

Related Questions