How do I convert an integer that is stored in a varchar table to hour and minute?
The data is saved in MySQL in this way example: 1030
and I need to show it as 10:30 am
.
Can a conversion be done in PHP or from MySQL?
How do I convert an integer that is stored in a varchar table to hour and minute?
The data is saved in MySQL in this way example: 1030
and I need to show it as 10:30 am
.
Can a conversion be done in PHP or from MySQL?
You can do it using the
substr
PHP function.Example:
Then we proceed to concatenate:
More information about
substr
Here: http://php.net/manual/es/function.substr.phpWith the function
date_create
anddate_format
example: specifying
H:i:s
so that it only considers the hour-min-secAnother valid way, using a DateTime object and its factory
createFromFormat
It can also be done in the MySQL SELECT, without the need to go through PHP.
I show 4 columns in the query: one with the original data as it would appear in the table... considering that there may be typing errors, another with the correct time , that is, forcing it to have 4 digits, in the case of values such as
130
, for this, one is added0
to the left using LPAD , another with the time in AM/PM format and another with the time in 24H format .The query, as I have already said, is designed to correct possible erroneous values and also handles values that are entered in 24h format, such as
1330
.The final results (columns 3 and 4) are actually data of type
DATETIME
, obtained with MySQL's STR_TO_DATE() function . And since it's data of typeDATETIME
, you can display the results however you want, simply by modifying the format specifiers . In this case I have used'%r'
and'%H:%i'
, but they can be changed to any other valid one :)SQL-Fiddle
MySQL 5.6 Schema Setup :
Query 1 :
Results :
I think the simplest, fastest and most valid way would be this:
This will convert you to the following:
And, with this output, it can be displayed with AM or PM as follows:
A complete example :
Output: