[FIXED] how to convert time saved as int in mysql

Issue

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()?

Solution

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

or

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

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

Answered By - Akina

Answer Checked By - Katrina (PHPFixing Volunteer)