how to convert time saved as int in mysql-CodePudding

I have a column time in a table which is saved as an int in a 24hours format. Example : 1900 (meaning 7:00pm) or 1000(10:00 am) or 930 (9:30 am) What's the simplest way for me to convert it to time to be able to compare it with now()?

CodePudding user response：

SELECT CAST(`time`*100 AS TIME) FROM table

SELECT TIME_FORMAT(`time`*100, '%h:%i %p') FROM table

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f2f08682a6f8cf8410390b8998f7a800

CodePudding user response：

a query like the below will help you convert the int you got into time :

SELECT CAST(INSERT(CAST(LPAD(1200,4,0) AS CHAR(50)), 3, 0, ':') as TIME);